Need SQL Query to find Parent records without child records

Question

I am not at all conversant in SQL so was hoping someone could help me with a query that will find all the records in a parent table for which there are no records in a child table.

The following works for me to find parent records for specific child field values...

`SELECT    ParentTable.ParentID  FROM      ParentTable INNER JOIN              ParentTable ON ParentTable.ParentID = ChildTable.ChildID  WHERE     (ChildTable.ChildField_ = '2131')  Group By    ParentTable.ParentID  Having    count(distinct ChildTable.ChildField) > 0` 

Can I change the where clause some how to find parent's with a count of zero child records.

Thanks.

Answer 1

You can use a NOT EXISTS clause for this

SELECT ParentTable.ParentID FROM ParentTable WHERE NOT EXISTS (     SELECT 1 FROM ChildTable     WHERE ChildTable.ParentID = ParentTable.ParentID ) 

There's also the old left join and check for null approach

SELECT ParentTable.ParentID FROM ParentTable LEFT JOIN ChildTable   ON ParentTable.ParentID = ChildTable.ParentID WHERE ChildTable.ChildID IS NULL 

Try both and see which one works better for you.

Answer 2

Outer join parent to child, and then having count(*) = 0.

select   p.parent_id,   count(*) from   parent p left outer join child c on p.parent_id = c.parent_id group by   p.parent_id having   count(*) = 0