CODEWITHSUNDEEP
mysqlsqljoin
morgan
morgan

Reputation: 13

return single rows from one table that don't match other table

so i have two tables. I'm trying to join two tables and only return records from table a that don't have a match in table b. i'm stumped so far.

Table A

--------------------
name amount     date
--------------------
bob     250 4/8/2010
dan     100 4/8/2010
sla     222 4/8/2010
obo     344 4/8/2010
bob     100 4/8/2010

Table B

--------------------
name amount     date
--------------------
bob     250 4/8/2010
dan     100 4/8/2010
sla     500 4/8/2010
obo     300 4/8/2010
bob     100 4/8/2010

Upvotes: 1

Views: 205

Answers (2)

Yawar
Yawar

Reputation: 11627

One of the canonical ways to do this in SQL is using an outer join:

select a.*
from a left outer join b
on (
  a."name" = b."name"
  and a."amount" = b."amount"
  and a."date" = b."date"
) where b."name" is null
and b."amount" is null
and b."date" is null

The left outer join returns all rows from the table on the left of the join, whether or not they match rows from the table on the right side of the join. But when rows from the left table don't have a match in the right table, the columns of the right table are set to null. You can thus filter out all rows that did match and return only rows from the left table that didn't match.

Upvotes: 1

potashin
potashin

Reputation: 44581

You can use EXISTS :

SELECT * 
FROM a 
WHERE NOT EXISTS (SELECT * 
                  FROM b 
                  WHERE name = a.name 
                    AND amount = a.amount 
                    AND date = a.date)

Upvotes: 2

Related Questions