CODEWITHSUNDEEP
mysqlsqldatabasejoin
Michael Samuel
Michael Samuel

Reputation: 3920

Joining two tables and selecting rows not present in the second 1

I have a table called users with a user called Bob like this:

username
  Bob

I have another table called mail with the following rows:

sender | receiver
Marley | Bob
 Saget | Bob

I have another table called block with the following rows:

blocker | blocked
 Marley | Bob

I want to select users from the table user and the number of messages they received BUT ignore messages from blocked users. So the result should be like this:

users.username | count_mail 
 Bob           |  1

So as you can see I need to get Bob along with the number of messages he received which is only 1 since Bob blocked Marley so the message by Saget should be the only 1 counted.

I use the following query but it returns nothing:

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN block ON (block.blocker = users.username) 
LEFT JOIN mail ON (mail.receiver = users.username 
                   AND mail.sender <> block.blocked)

Thanks

Upvotes: 0

Views: 208

Answers (2)

Bohemian
Bohemian

Reputation: 424973

Try this:

SELECT users.username, COUNT(mail.receiver) AS count_mail
FROM users 
LEFT JOIN mail ON mail.receiver = users.username 
LEFT JOIN block ON block.blocker = users.username
    AND mail.sender = block.blocked
WHERE block.blocked IS NULL
GROUP BY 1

This works because it only gets hits when the left join to blocker fails - asserted by the where clause tasting for a non-join.

Upvotes: 1

rs.
rs.

Reputation: 27417

Try this

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN mail ON (mail.receiver = users.username)
WHERE (mail.sender is null 
         or NOT EXISTS
             (SELECT 1 FROM block 
              WHERE mail.sender = block.blocker
              AND mail.receiver = block.blocked)
      )

OR 

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN (SELECT * FROM mail WHERE NOT EXISTS
                 (SELECT 1 FROM block 
                   WHERE mail.sender = block.blocker
                   AND mail.receiver = block.blocked)
          ) mail ON (mail.receiver = users.username)

Upvotes: 1

Related Questions