user2325417 user2325417 - 3 months ago 22
SQL Question

Simple/ish command

which join do you use to select data from parent and child table, where the parent table may contain no child data?

Answer

I think your question is more properly stated as this:

How to find items in the master table which don't have any items in the child table?

That is a very common question in SQL, and there is a known solution... this works in T-SQL (you need to always say what you're using)

select m.master_data, c.child_data
from master_table m
left outer join child_table c 
    on m.ID = c.ID
where c.child_data IS NULL

When there is nothing in the child table, the OUTER join produces nulls in the child table columns, so you just show the rows with nulls in the child table and that's it. You don't show any columns from the child table in your result.