需要 SQL 查询来查找没有子记录的父记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6839500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need SQL Query to find Parent records without child records
提问by user278859
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.
我根本不熟悉 SQL，所以希望有人可以帮助我进行查询，以查找父表中的所有记录，而子表中没有记录。
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.
我可以更改 where 子句，以了解如何找到具有零子记录计数的父项。
Thanks.
谢谢。
回答by Phil
You can use a NOT EXISTSclause for this
您可以NOT EXISTS为此使用子句
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 nullapproach
还有旧的左连接并检查空方法
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.
两者都试一下，看看哪一个更适合你。
回答by rkaregaran
Outer join parent to child, and then having count(*) = 0.
外连接父子，然后 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
回答by Pipo
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
NOTEXISTS
请注意，唯一改变的是
NOTEXISTS
Hope it helps
希望能帮助到你
回答by Himanshu Ahuja
I simply dont understand whats the havingclause 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.
我只是不明白having你的查询中的子句在做什么，因为我已经看到你说 where
ChildTable.ChildField_ = '2131'这已经意味着你已经为 childfield 2131 设置了记录尝试下面的查询，这意味着如果父级在带有字段 2131 的 Childtable 中没有子级，那么 o/ p 一样。
SELECT ParentTable.ParentID
FROM ParentTable
Where ParentTable.ParentID NOT IN (Select ChildID
From ChildTable where
ChildTable.ChildField_ = '2131')
回答by Grisha Kamyshnikov
If you do a left join on the child table and simply say where the child parentID is null.
如果您在子表上执行左连接并简单地说明子 parentID 为空的位置。
SELECT ParentTable.ParentID FROM ParentTable P
Left Join ChildTable C on C.ParentID = P.ParentID
WHERE C.Id IS NULL;
回答by sovantha
You can try NOT IN
你可以试试 NOT IN
SELECT * FROM ParentTable WHERE ParentID NOT IN (SELECT DISTINCT ParentID FROM ChildTable)
