CODEWITHSUNDEEP
mysqlsqljoin
Matteo Tassinari
Matteo Tassinari

Reputation: 18584

How to select record except those that match the join?

In our webapps, an employee can perform one or more jobs at the same time, so the database is structured like this:

http://rextester.com/VTFND62465

table tma_employee:

emp_id | emp_name
-------|---------
1      | BOB
2      | MAX
3      | SALLY

table tma_job:

job_id | job_name
-------|---------
1      | DEV
2      | SALES
3      | MGMT

relation table between employees and jobs tma_employee_job:

emp_id | job_id
-------|-------
1      | 1
1      | 2
2      | 2
2      | 3
3      | 3
3      | 1

Given this structure, it is easy to select all employees which perform one or more specific jobs:

SELECT DISTINCT E.*
FROM tma_employee E
JOIN tma_employee_job EJ USING (emp_id)
JOIN tma_job J USING (job_id)
WHERE J.job_name = 'DEV';

However, how can I select all employees which do not perform that job?

I tried inverting the condition like this:

SELECT DISTINCT E.*
FROM tma_employee E
JOIN tma_employee_job EJ USING (emp_id)
JOIN tma_job J USING (job_id)
WHERE J.job_name != 'DEV';

However this does not work because, for example, BOB is also on the SALES job, which is different from DEV, so employee BOB is found from the above query.

I have also found that this works:

SELECT DISTINCT E.*
FROM tma_employee E
WHERE E.emp_id NOT IN (
    SELECT EJ.emp_id
    FROM tma_employee_job EJ
    JOIN tma_job J USING (job_id)
    WHERE J.job_name = 'DEV'
);

However, it requires me to use a subquery, which I would like to avoid, because the main query is built by a function which, based on the parameters it receives, starts from SELECT E.* FROM tma_employee E and then adds all the various JOINS and clauses.

Is it possible to obtain the same result of the query which uses the sub-query, while sticking to simple JOINS?

Upvotes: 2

Views: 62

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL DEMO without subquery:

SELECT E.emp_id, E.emp_name
FROM tma_employee E
LEFT JOIN tma_employee_job EJ USING (emp_id)
LEFT JOIN tma_job J ON J.job_id = EJ.job_id
GROUP BY E.emp_id, E.emp_name
HAVING COUNT(CASE WHEN job_name = 'DEV' THEN 1 END) = 0;

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT DISTINCT E.*
FROM tma_employee E
LEFT JOIN (
    SELECT EJ.emp_id
    FROM tma_employee_job EJ
    JOIN tma_job J USING (job_id)
    WHERE J.job_name = 'DEV'
) T ON E.emp_id = T.emp_id 
WHERE T.emp_id IS NULL

Upvotes: 1

Related Questions