Please forgive the general nature of this question, but I am struggling to find good advice on how to handle this problem.
Assume I have two SQL tables, as follows:
Employees: id, name, department_id
1, 'John', 1
2, 'Mary', 3
3, 'Cathy', 2
4, 'Karen', 1
Departments: id, name
1, 'Sales'
2, 'Admin'
3, 'Shop floor'
When I create a new employee, I would like to allow the user of the application to leave the department blank to begin with. But when I create the database record, I obviously have to supply a
department_id foreign key value. At the moment, I am using
-1, which can never be a legal foreign key.
My problem comes when trying to write the SQL to retrieve all the unique employees with no duplicates.
If I simply use...
SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;
...then I don't get any of the new employees (i.e. the ones who have not yet been assigned a department). This means the user of the application can't access these new employees and put them in a department.
If on the other hand, I use...
SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id OR e.department_id = -1;
...I get lots of duplicate records because every employee with no department is included in the result once for each department.
I would really appreciate some advice on how best to tackle this.
What I have tried:
I have tried adding a dummy department as the first record and assigning new employees to that, but then I have to filter this out from the UI when I am doing things like presenting a list of departments, which seems less than ideal.