Question 112732 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find combinations of columns 1 and 2 that do not exist in an SQL table?

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
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    You can use aggregation.

    If you want just the branches:

    SELECT branch
FROM tablename
GROUP BY branch 
HAVING COUNT(DISTINCT role) = (SELECT COUNT(DISTINCT role) FROM tablename);

    If you want all the rows of the table for the branches returned by the above query use also the operator IN:

    SELECT *
FROM tablename
WHERE branch IN (
  SELECT branch
  FROM tablename
  GROUP BY branch 
  HAVING COUNT(DISTINCT role) = (SELECT COUNT(DISTINCT role) FROM tablename)
);

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search