MySQL query rows that are in table_b and not in table_a

Question

Hello. I'm doing this query to get rows that are in table_b and not in table_a:

SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULL

This works ok but I need another condition to apply on table_a. I need to compare only the rows that got column X equal with the ID of 3, not to compare the whole table. Any ideas ?

Thanks.

Answer 1

I'm not sure if you're looking for table_a column x or table_b column x, but the idea is to add that to the join ON clause:

LEFT JOIN table_a ON table_b.id = table_a.id and table_a.id = 3

Answer 2

Try this out...

SELECT table_b.* 
FROM table_b
LEFT JOIN table_a 
  ON table_b.id = table_a.id
  AND table_a.X = 3
WHERE table_a.item_id IS NULL

Should do the trick...

But this is a bit strange, as it will probably return more than you require. This query will also return any rows from table_b which do have a result in table_a, but where those rows don't have an 'X' value of 3.

If it doesn't work, please try to give an example for data that you need to find, and data that you don't want, and we can try to help further!