CODEWITHSUNDEEP
mysqlsql
Gregorio
Gregorio

Reputation: 307

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?

Upvotes: 3

Views: 3161

Answers (4)

Philipp
Philipp

Reputation: 2796

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.

Upvotes: 2

clifton_h
clifton_h

Reputation: 1298

You are using an OUTER JOIN, so take advantage of it.

  • Outer joins return all of the rows on one side of the join regardless of whether there is a match or not.
  • Therefore, when you run a PREDICATE on the (outer) QUERY, you are eliminating the rows that would return the values you still want to see.

You could do something like the following:

SELECT Name, B.Trans_ID, Stud_ID B.Payment_Month
FROM student_list A
LEFT OUTER JOIN (SELECT TRANS_ID, PAYMENT_MONTH, Stud_ID
                 FROM   transaction_list
                 WHERE  PAYMENT_MONTH = "January") B
ON A.Stud_ID = B.Stud_ID

It will return all the matches on ID where transaction_list is in January as well as the ones not matched in student_list. TO be fair, you might have been wanting to eliminate duplicates in your query, so you can stil accomplish this in the GROUP BY clause.

Upvotes: 2

Gregorio
Gregorio

Reputation: 307

Thanks to @Philipp

This is the code that worked for me :

SELECT Stud_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 Stud_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

Even if I change "January" to "March", it worked as well as I wanted.

Upvotes: 0

Blank
Blank

Reputation: 12378

Try this;)

SELECT Name, COALESCE(Trans_ID, '-') AS Trans_ID, COALESCE(Payment_Month, '-') AS Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON t.Stud_ID = s.Stud_ID
AND NOT EXISTS (
    SELECT 1 FROM transaction_list tl WHERE tl.Stud_ID = t.Stud_ID AND tl.Payment_Month = 'January'
)

SQLFiddle DEMO HERE

Upvotes: 0

Related Questions