Finding orphans 1

[Sen7inel]

If I have table_a.id and table_b that has table_a_id, how can I find all entries from table_b that have a non-existant table_a_id?

 

[TonyGroves]

How about:
[tt]
SELECT table_b.*
FROM
table_a
LEFT JOIN table_b ON table_a.id=table_b.table_a_id
WHERE table_a.id IS NULL
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[Sen7inel]

I guess my post was a bit unclear =(. I meant finding such rows from table_b, that have a table_a_id that simply doesn't exist at all in table_a's id column.

 

[TonyGroves]

That code should do what you want. Have you tried it?

-----
ALTER world DROP injustice, ADD peace;

 

[Sen7inel]

Yes, I tried it, and it returned a bunch of all-NULL rows. However, RIGHT JOIN seems to work (or then I typo'd something really bad somewhere). =)

 

[TonyGroves]

You're right, it should have been RIGHT JOIN. Sorry about that!

-----
ALTER world DROP injustice, ADD peace;