MySQL& quot;真正与众不同"加入 [英] MySQL "truly distinct" join

问题描述

我已经为此战斗了几个小时，并且一直在努力使用正确的MySQL语法.对于我正在寻找的那种SQL的任何指针，将不胜感激.

I've been fighting this one for a couple of hours now and struggle with the proper MySQL syntax to use. Any pointer on the kind of SQL I'm looking for will be greatly appreciated.

请考虑以下两个简单表:

Please consider those two simple tables:

purchase:
id  email   product_id
1   [e-mail]    1
2   [e-mail]     2
3   [e-mail]     1
4   [e-mail]     2

subscriber:
id  email   name
1   [e-mail]    Andy
2   [e-mail]     Bob
3   [e-mail]     Charly

我要选择不购买product_id 1的订户.即:在这种情况下，它只应返回鲍勃的行.

I'd like to select subscribers who did not buy product_id 1. ie: in this case, it should ONLY return Bob's row.

但是，我目前正在使用此...

However, I'm currenty using this...

SELECT DISTINCT subscriber.*
FROM subscriber
LEFT OUTER JOIN purchase
ON subscriber.email = purchase.email
WHERE ( purchase.product_id <> 1 )

...返回以下内容:

...which returns this:

id  email   name
2   [e-mail]     Bob
3   [e-mail]     Charly

我知道我需要处理人们在购买"表中两次出现的情况.我该怎么办?

I understand that I need to handle the case of people showing up twice in the 'purchase' table. How can I do that?

谢谢你的指导！

Fabien

推荐答案

只需使用以下内容:

SELECT DISTINCT subscriber.*
FROM subscriber
LEFT OUTER JOIN purchase
ON (subscriber.email = purchase.email and purchase.product_id = 1)
WHERE purchase.product_id IS NULL

这篇关于MySQL& quot;真正与众不同"加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！