MySQL LEFT JOIN Multiple Conditions

Question

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

User · Answer

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

User · Answer

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

User · Answer

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] MySQL LEFT JOIN Multiple Conditions

Examples related to mysql

Examples related to join