Left Join and remove duplicates-CodePudding

I have three tables, clients, job_allocations and jobs table. I want to select all clients that are not in a particular job, below are my tables.
Clients table

id	Fullname
1	John Doe
2	Jane Doe
3	King James
4	Jere Gray

Jobs table

id	Title
1	Road Construction
2	Repair of Engines

job_allocations table

id	client_id	job_id
1	2	1
2	2	2
3	1	2
4	3	2

I want to select all clients that are not in job_id=2, but when I ran my query, I am getting client id: 2 - Jane Doe again, please how do I solve this?

I did this:

LEFT JOIN job_allocations ON job_allocations.client_id = clients.id
WHERE job_id <> 2 OR job_id IS NULL```

CodePudding user response：

You can use a NOT IN clause as follows:

SELECT * 
FROM clients
WHERE id NOT IN (SELECT client_id 
                 FROM job_allocations 
                 WHERE job_id = 2)

Check the demo here.

CodePudding user response：

So you will fetch all clients, but only jobs related to job_id <> 2

This query should work for you:

SELECT client.* 
FROM clients
LEFT JOIN job_allocations ON job_allocations.client_id = clients.id and job_id <> 2

CodePudding user response：

Use DISTINCT keyword for selecting unique values