CODEWITHSUNDEEP
mysqlsqldatabasejoin
Michael Samuel
Michael Samuel

Reputation: 3920

MYSQL - Select only if row in LEFT JOIN is not present

I have 2 simple mysql tables. The first 1 called mail and has 2 rows:

sender | receiver
Marley | Bob 
Saget  | Bob

The second one called block and has 1 row:

blocker | blocked
  Bob   | Marley

I want to select sender(s) from the first table who sent Bob emails but aren't blocked in the block table. So the results should be:

sender 
 saget

I tried the following query but it's not returning results:

SELECT * FROM mail  
LEFT JOIN block ON (block.blocker = 'Bob') 
WHERE (block.blocked <> mail.sender)

Upvotes: 20

Views: 28925

Answers (2)

Johan
Johan

Reputation: 76537

The left join will produce null rows for the mismatches.
It's those null rows that you need to filter on. 

SELECT * FROM mail  
LEFT JOIN block ON (block.blocker = 'Bob') 
WHERE block.blocker IS NULL

It's kind of strangle to be joining on a fixed value however, a more common join (given your tables) would be:

SELECT * FROM mail  
LEFT JOIN block ON (block.blocker = mail.receiver
                and block.blocked = mail.sender)<<-- these should match
WHERE block.blocker IS NULL                     <<-- select only mismatches
AND mail.receiver like 'bob';

Upvotes: 23

Lamak
Lamak

Reputation: 70638

Try this:

SELECT sender
FROM mail m
WHERE NOT EXISTS (SELECT 1 FROM block 
                  WHERE blocker = m.receiver 
                  AND blocked = m.sender)

Upvotes: 13

Related Questions