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.
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"mysqlleft-joinright-join
Select A.* from A where A.Id not in (select ref from B where B.ref = A.Id and B.Status = 'X')
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