CODEWITHSUNDEEP
mysqlsql
billyonecan
billyonecan

Reputation: 20270

Retrieve rows where all related data meets specified criteria

I have a job table and a visit table. A job can have multiple visits. I need to retrieve all jobs, which haven't been set as paid, with all visits tied to that job set as completed.

So basically I need to only retrieve a job if:

Obviously doing the following doesn't work as it will return any result where job.paid = 'N' and visit.status = '2':

SELECT * 
FROM   job INNER JOIN visit 
       ON job.id = visit.job_id 
WHERE  job.paid = 'N' AND 
       visit.status = 2;

I could retrieve the results, and run additional queries to check that all the visits for a job are complete, but I was wondering if it's possible to retrieve the data in a single query?

Upvotes: 3

Views: 143

Answers (4)

Aushin
Aushin

Reputation: 1208

SELECT * 
FROM job j 
WHERE j.paid = 'N' AND 
  NOT EXISTS (SELECT 1 FROM visit WHERE job_id = j.id AND visit.status <> 2);

Upvotes: 1

John Woo
John Woo

Reputation: 263933

UPDATE 1

SELECT  a.ID  -- <<== add some columns here
FROM    job a INNER JOIN visit b ON a.id = b.job_ID
WHERE   a.paid = 'N'
GROUP   BY a.ID
HAVING  COUNT(DISTINCT b.Status) = 1 AND MAX(b.status) = 2

Upvotes: 1

Jim
Jim

Reputation: 6881

If you have the possibility of a Job that doesn't have any Visit records associated with it, and you'd want to still return that Job record if it has paid = 'N', then you'll want to LEFT JOIN.

SELECT * 
FROM JOB j
LEFT JOIN VISIT v
ON j.id = v.job_id
WHERE j.paid = 'N'
AND j.id NOT IN (SELECT job_id FROM visit WHERE status != 2)

Upvotes: 1

Nagasaki
Nagasaki

Reputation: 58

SELECT * FROM job WHERE paid = 'N' AND id NOT IN (
SELECT job_id FROM visit WHERE status != 2)

Upvotes: 1

Related Questions