MySQL LEFT JOIN Multiple Conditions

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

A
group_id
keyword

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.

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.

Any help would be appreciated! Thanks

Answer 1

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

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.

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.

demo here

Above answers are correct. Another way to achieve this is;

SELECT a.group_id
FROM a 
LEFT JOIN b ON (a.group_id, b.user_id) = (b.group_id, 4)
where b.user_id is null
  and a.keyword like '%keyword%'

Complete Example