Suppose the table is:
| employee_id | branch | role |
|---|---|---|
| 1 | A | admin |
| 2 | A | engineer |
| 3 | A | finance |
| 4 | B | admin |
| 5 | B | finance |
| 6 | C | engineer |
How can I find the departments that do not have all the roles?
In this example:
- Department A has all the roles.
- Department B does not have engineer role.
- Department C does not have admin and finance roles.
What would be the SQL query to get this result?
Ideally, the output should be
| branch | role |
|---|---|
| B | engineer |
| C | admin |
| C | finance |
CodePudding user response：
We can use a calendar table left anti join approach here:
SELECT b.branch, r.`role`
FROM (SELECT DISTINCT branch FROM yourTable) b
CROSS JOIN (SELECT DISTINCT `role` FROM yourTable) r
LEFT JOIN yourTable t
ON t.branch = b.branch AND
t.`role` = r.`role`
WHERE t.branch IS NULL
ORDER BY SELECT b.branch, r.`role`;
Note that role was a reserved keyword in MySQL versions earlier than 8 . If you are using an earlier version, you should avoid using role as a column name.
CodePudding user response：
We can use CROSS JOIN to build all possible combinations of branch and role and then NOT EXISTS to exclude those combinations that really exist in the table:
SELECT DISTINCT b.branch, r.roles
FROM (SELECT branch FROM yourtable) b
CROSS JOIN (SELECT roles FROM yourtable) r
WHERE NOT EXISTS (SELECT 1 FROM yourtable
WHERE branch = b.branch AND roles = r.roles)
ORDER BY b.branch, r.roles;
Note: The column "role" was renamed to "roles" in my query to prevent that "role" will be read as SQL key word.