CODEWITHSUNDEEP
mysql
Jozomby
Jozomby

Reputation: 49

SQL Query to find missing entries

I have 3 tables:

Applications

Reviewers

Reviews

(*Note that I removed the table columns that are irrelevant, such as name, title, etc.)

Reviewers are assigned multiple applications, and submit 1 review for each application. So, an application should have 2 reviews associated with it, both from different reviewers.

I need to write a MySQL query to select all reviewers that have not yet completed reviews for each application to which they're assigned.

I've been working at this problem for quite a while, but the solution is eluding me. I can find all reviewers associated with applications that have not received any reviews yet, but if an application has only 1 review but not the other completed, I'm at a loss.

Upvotes: 1

Views: 55

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

If I understand correct. You assign Reviewer on the Application first. And when Reviewer finish the review appear on Reviews

So you need use LEFT JOIN to find what reviews arent complete.

SELECT A.id, reviewer1_id
FROM Applications A
LEFT JOIN Reviews R
       ON A.reviewer1_id = R.reviewer_id
      AND A.id = R.application_id
WHERE R.id IS NULL

UNION ALL

SELECT A.id, reviewer2_id
FROM Applications A
LEFT JOIN Reviews R
       ON A.reviewer2_id = R.reviewer_id
      AND A.id = R.application_id
WHERE R.id IS NULL

If you also neeed Reviewer name will need one aditional JOIN 

SELECT A.id, A.reviewer1_id, RV.name
FROM Applications A
LEFT JOIN Reviews R
       ON A.reviewer1_id = R.reviewer_id
      AND A.id = R.application_id
     JOIN Reviewers RV
       ON A.reviewer1_id = RV.id
WHERE R.id IS NULL

Upvotes: 2

Dan
Dan

Reputation: 11104

SELECT * FROM Applications a 
JOIN Reviewers r1 ON a.reviewer1_id = r1.id
JOIN Reviewers r2 ON a.reviewer2_id = r2.id
LEFT JOIN Reviews rv1 ON r1.id = rv1.reviewer_id
LEFT JOIN Reviews rv2 ON r2.id = rv2.reviewer_id
WHERE rv1.id IS NULL OR rv2.id IS NULL

Upvotes: 0

Related Questions