[SOLVED] SQL WHERE IN with Left Join is not returning all rows I expect

SQL WHERE IN with Left Join is not returning all rows I expect

I'm building a little conjugation/radicalization app, and I have stumbled upon a problem. I have this SQL request:

SELECT    DISTINCT RA.* 
FROM      radical RA 
left join conjugated CO 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat')

That returns:

| id | radical |
| 13 |  to be  |

However, I would like to get a result of the type:

| id   | radical | word |
| null |  null   |  I   |
|  13  | to be   | am   |
| null |  null   |  a   |
| null |  null   | cat  |

Does anyone know how?

Solution

Seemingly you are using a Left Join, when you actually need a Right join (since it appears you want all rows of the right table matching the predicate to be returned)

So either switch the join:

SELECT    DISTINCT RA.*, co.`conjugated` as word
FROM      radical RA 
right join conjugated CO 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat');

Or switch the order of the tables in the FROM:

SELECT    DISTINCT RA.*, co.`conjugated` as word
FROM      conjugated CO 
left join radical RA 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat');