Search code examples
mysqlleft-joinright-join

MySql Joint with having one of multiple right occurence

I have 2 table (A and B). Each row on table A have up to 3 correspondence on table B. Table B have a status field that can be either "x" "y" or "null".

I want to get all row from table A where no matching row from table B have the "X" status (see below picture). So basically I want row with Id 2 and 3.

enter image description here

Note, my from statement MUST BE on Table A.

So far I tried without success the following :

select *
from table A
left join table B on a.Id = b.ref
where status = 'Y'
or status is null;

select *
from table A
right join table B on a.Id = b.ref
where status = 'Y'
or status is null;

problem is that for every row from table A, I have a row in table B that satisfy the where condition so I get all 3 row returned instead of only the one in green. I need to do something like "having all matching row from table B != "X"

Solution

  • The idea is you try to match the row in table A with anyone on table B but only those with x on it.

    If you cant you will get NULL and those are the rows you want.

     SELECT A.*
 FROM TableA as A
 LEFT JOIN TableB as B
   ON A.id = B.Ref
  AND B.status = 'x'
 WHERE B.status is null