MySql Joint with having one of multiple right occurence

Question

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"

You could find all the TableA records that have an X then select again subtracting those. Select * from TableA where id not in (select id from tableA left join TableB on a.id = b.id and status = 'x'); — Daniel Gale, Mar 13 '18 at 18:08

Juan Carlos Oropeza · Accepted Answer · 2018-03-13T18:21:59.830

2

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

edited Mar 13 '18 at 18:21

answered Mar 13 '18 at 18:09

Juan Carlos Oropeza

47,252
12
78
118

@DanielGale Nope, you are getting the ones doesnt matching any table B – Juan Carlos Oropeza Mar 13 '18 at 18:19
I tried in mysql and it worked perfectly. So basically, you are joining on b.status = 'x' which will return only one record from table B. then checking for null on right side which is totally working and brilliant. thanks for the answer. didnt knew we could have 2 conditions on the jointure. – Yannick Richard Mar 13 '18 at 18:31
Your JOIN condition can be as complex as you want. Remember accept the answer if solve the problem – Juan Carlos Oropeza Mar 13 '18 at 18:35

score 1 · Answer 2 · edited Mar 13 '18 at 18:23

1

Select A.* 
from A 
where A.Id not in (select ref 
                   from B 
                   where B.ref = A.Id 
                     and B.Status = 'X')

edited Mar 13 '18 at 18:23

Juan Carlos Oropeza

47,252
12
78
118

answered Mar 13 '18 at 18:08

Hussein Salman

7,806
15
60
98

you dont need left join. just the correlated query. – Juan Carlos Oropeza Mar 13 '18 at 18:20
yes you are right! I missed it completely. Thanks though! – Hussein Salman Mar 13 '18 at 18:24

MySql Joint with having one of multiple right occurence

2 Answers2