MYSQL-仅当LEFT JOIN中的行不存在时选择 [英] MYSQL - Select only if row in LEFT JOIN is not present

问题描述

我有2个简单的mysql表。前1个称为mail，并有2行：

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

sender | receiver
Marley | Bob 
Saget  | Bob 

第二个称为块并具有1行：

The second one called block and has 1 row:

blocker | blocked
  Bob   | Marley

我想从第一个表格中选择发送Bob电子邮件但没有发送电子邮件的发件人在阻止表中被阻止。因此结果应该是：

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)

推荐答案

左连接将为不匹配项生成 null 行。

这是需要过滤的 null 行。

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';

这篇关于MYSQL-仅当LEFT JOIN中的行不存在时选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！