MySQL LEFT JOIN 多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16637929/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL LEFT JOIN Multiple Conditions
提问by Jon Robinson
I have two tables: A and B linked by "group_id".
2 variables I'm using: $keyword, $_SESSION['user_id']
我有两个表：A 和 B 由“group_id”链接。
我正在使用的 2 个变量：$keyword, $_SESSION['user_id']
A
group_id
keyword
一个
GROUP_ID
关键字
B
id
group_id
user_id
B
id
group_id
user_id
I want to be able to select all the groups that this user is not in based on a keyword search.
我希望能够根据关键字搜索选择该用户不在的所有组。
Therefore the goal is to SELECT all the rows in A WHERE the user_id!={$_SESSION['user_id'} for the corresponding group_id in B AND like the keyword.
因此，目标是选择 A 中的所有行，其中 user_id!={$_SESSION['user_id'} 对应 B 中的相应 group_id 和关键字。
this is what I tried:
这是我试过的：
SELECT a.*
FROM a
LEFT JOIN b ON a.group_id=b.group_id
WHERE a.keyword LIKE '%".$keyword."%'
AND b.user_id!=$_SESSION{['user_id']}
GROUP BY group_id
However, it does not find any rows (matches) unless I remove AND b.user_id!=$_SESSION{['user_id']}in which case it will also include groups the user is already in - which is not what I want.
但是，除非我删除AND b.user_id!=$_SESSION{['user_id']}，否则它不会找到任何行（匹配项），在这种情况下，它还将包含用户已经在其中的组 - 这不是我想要的。
Any help would be appreciated! Thanks
任何帮助，将不胜感激！谢谢
回答by Bryan
Just move the extra condition into the JOIN ON criteria, this way the existence of b is not required to return a result
只需将额外的条件移动到 JOIN ON 条件中，这样 b 的存在就不需要返回结果
SELECT a.* FROM a
LEFT JOIN b ON a.group_id=b.group_id AND b.user_id!=$_SESSION{['user_id']}
WHERE a.keyword LIKE '%".$keyword."%'
GROUP BY group_id
回答by pala_
Correct answer is simply:
正确答案很简单：
SELECT a.group_id
FROM a
LEFT JOIN b ON a.group_id=b.group_id and b.user_id = 4
where b.user_id is null
and a.keyword like '%keyword%'
Here we are checking user_id = 4(your user id from the session). Since we have it in the join criteria, it will return null values for any row in table b that does not match the criteria - ie, any group that that user_id is NOT in.
在这里，我们正在检查user_id = 4（您的会话中的用户 ID）。由于我们在连接条件中有它，它将为表 b 中与条件不匹配的任何行返回空值 - 即 user_id 不在的任何组。
From there, all we need to do is filter for the null values, and we have all the groups that your user is not in.
从那里，我们需要做的就是过滤空值，我们拥有您的用户不在的所有组。
回答by Lance Cleveland
SELECT * FROM a WHERE a.group_id IN
(SELECT group_id FROM b WHERE b.user_id!=$_SESSION{'[user_id']} AND b.group_id = a.group_id)
WHERE a.keyword LIKE '%".$keyword."%';
