CODEWITHSUNDEEP
mysql
Tomatrox
Tomatrox

Reputation: 693

Joining a table and selecting where NOT exists

I have the following tables: enter image description here

employees: A table with all my employees
teams: A table with teams created
team_employees: A table with a relation of a team with a list of employees.

So, basically what I need is to select all the employees that don't have a team, within a team. For example, if you see, in the table team_employees, the id_team #2 doesn't have the id_employee #2. The query select I'm looking for should give me the ID employee #2 (but only of the Team ID #2)

I've already tried doing a LEFT JOIN like this but no luck, as I thought it would give me a NULL in the team_id field, but nope: 

SELECT te.id_team AS id_team, e.id, e.name
FROM employees e
LEFT JOIN team_employees te
ON te.id_employee = e.id
WHERE id_team = 2
GROUP BY e.id

Thanks for helping!

Upvotes: 1

Views: 33

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Use this form of left join

SELECT te.id_team AS id_team, e.id, e.name
FROM employees e
LEFT JOIN team_employees te ON te.id_employee = e.id AND te.id_team = 2
WHERE te.id_team IS NULL

Sometimes called "left excluding join". What you need to do is place the e.id_team = 2 into the join conditions, not the where clause. Then the where clause is used to locate all rows with no match to the team table.

Alternative:

SELECT e.*
FROM employees e
WHERE NOT EXISTS (
     select null 
     from team_employees te
     where te.id_employee = e.id AND te.id_team = 2
     )

Upvotes: 2

Related Questions