ok, then you're going to have to create them manually, and have a way of tracking them.
you can make an assumption on your data that they are consecutive, and count the branch employees to figure out the next number - that'll save you storing the last id to increment from, however it will create issues if you ever delete records, so i recommend against it.
You'll want a field for either employee branch id, that is separate to teh primary key. It can be unique if it's a varchar to store the full id in, or it can be an int, not unique, that you use to generate the id from. You'll also need a branch field that identifies their branch.
so, assuming, at most basic, you use this kind of structure:
CREATE TABLE `test`.`employees` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 32 ) NOT NULL ,
`branch` INT NOT NULL ,
`branch_id` INT NOT NULL
);
# and branches:
CREATE TABLE `test`.`branches` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 64 ) NOT NULL ,
`code` VARCHAR( 3 ) NOT NULL ,
UNIQUE (
`code`
)
);
assuming branches contains something like:
INSERT INTO `test`.`branches` (
`id` ,
`name` ,
`code`
)
VALUES (
'1', 'Sydney', 'SYD'
), (
'2', 'London', 'LON'
);
then when adding an employee, you first establish their branch id, assume it's sydney, so 1.
INSERT INTO employees ( name, branch, branch_id ) VALUES( 'employee name', 1, IFNULL( ( SELECT MAX( branch_id ) + 1
FROM `employees` tmp
WHERE branch =1 ),1 ) )
and then when getting the branch id:
SELECT CONCAT( branches.code, employees.branch_id ) coded_branch_id FROM employees INNER JOIN branches on branches.id = employees.branch
(of course add in all other fields and filters you like)
