G. Chiko G. Chiko - 5 months ago 5
SQL Question

How do I select rows which do not exists in another table

I want to create a report.

I have 2 tables :


  1. student_list

  2. transaction_list



Here's the rows included in those table

student_list

Stud_ID | Name
1 | Cat
2 | Dog
3 | Rabbit


transaction_list

Trans_ID | Stud_ID | Payment_Month
1 | 1 | January
2 | 1 | February


Now I want to select data from student_list which exists in transaction_list and combine it with data from student_list which do not exist in transaction_list where Payment_Month = "January"

I have tried this query

SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON t.Stud_ID = s.Stud_ID
WHERE Payment_Month = "January"
UNION
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL


And I got this

Name | Trans_ID | Payment_Month
Cat | 1 | January
Dog | - | -
Rabbit | - | -


But when I change the Payment_Month value in the query to "March", I got this

Name | Trans_ID | Payment_Month
Dog | - | -
Rabbit | - | -


Not as I want it, because I'd like this

Name | Trans_ID | Payment_Month
Cat | - | -
Dog | - | -
Rabbit | - | -


Is there anyway I can get that?

Answer
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID AND Payment_Month = "January"
UNION
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL

Try it like this. By using where you are filtering by the actual result your query delivers, so if your left join delivers null but you are filtering on that column the row is removed from the resultset.

By putting the month into the join condition it will just return null on this column (on the columns added by the join) and keep the row in the resultset.