MySQL LEFT JOIN多个条件 [英] MySQL LEFT JOIN Multiple Conditions

问题描述

我有两个表:A和B通过"group_id"链接.
我正在使用2个变量:$keyword, $_SESSION['user_id']

I have two tables: A and B linked by "group_id".
2 variables I'm using: $keyword, $_SESSION['user_id']

A
group_id
关键字

A
group_id
keyword

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.

因此，目标是在B AND中的相应group_id中选择user_id！= {$$ SESSION ['user_id'}的A中的所有行，就像关键字一样.

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.

这是我尝试过的:

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

但是，除非找到AND b.user_id!=$_SESSION{['user_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.

任何帮助将不胜感激！谢谢

Any help would be appreciated! Thanks

推荐答案

只需将额外条件移到JOIN ON条件中，这样就不需要b的存在来返回结果

Just move the extra condition into the JOIN ON criteria, this way the existence of b is not required to return a result

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

这篇关于MySQL LEFT JOIN多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！