Need SQL Query to find Parent records without child records

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.

---

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.

---

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

---

If you do a left join on the child table and simply say where the child parentID is null.

SELECT ParentTable.ParentID FROM ParentTable P
    Left Join ChildTable C on C.ParentID = P.ParentID
    WHERE C.Id IS NULL;

---

With another example as

Enumerate table

    id: SERIAL
    name: TEXT
    enumerate_id: INT

All parents who have children (all branches of a tree, even roots, but no leaf!)

SELECT id, name, enumerate_id
FROM enumerate p
WHERE EXISTS (
    SELECT 1 FROM enumerate c
    WHERE c.enumerate_id = p.id
);

All children who don't have children (all leafs of a tree)

SELECT id, name, enumerate_id
FROM enumerate p
WHERE NOT EXISTS (
    SELECT 1 FROM enumerate c
    WHERE c.enumerate_id = p.id
);

Note that the only one who changes is the NOT EXISTS

Hope it helps

---

You can try NOT IN

SELECT * FROM ParentTable WHERE ParentID NOT IN (SELECT DISTINCT ParentID FROM ChildTable)

---

I simply dont understand whats the having clause doing in your query as I see already you are saying where ChildTable.ChildField_ = '2131' that already means you have record set for childfield 2131 Try the below query it would mean that if the parent doesnt have child in the Childtable with field 2131then o/p the same.

     SELECT    ParentTable.ParentID
         FROM      ParentTable 
         Where ParentTable.ParentID NOT IN (Select ChildID 
        From ChildTable where
         ChildTable.ChildField_ = '2131')