SQL选择仅包含子项的父项(SQL select a parent with children only in)

如果你有父表

create table parent ( pid int not null, name varchar(255) )

和父子联接表

create table parent_child ( pid int not null, cid int not null, foreign key (pid) references parent(pid), foreign key (cid) references child(cid) ) create table child( cid int not null, name varchar(255) )

如何在下面的列表中找到所有孩子姓名的所有父母姓名（'dave'，'henry'，'myriam'，'jill'）。

我不想看到父母，如果他们有一个名字不同的孩子，但是如果他们有一个或多个孩子并且他们所有孩子的名字都在列表中我想看到父母的名字。

我确实找到了这个https://stackoverflow.com/a/304314/1916621 ，这将帮助我找到一个有这些名字的孩子的父母，但我无法弄清楚如何只有孩子的名字的父母该列表的子集。

如果有人知道不同方法的性能权衡，那么额外的分数。

if you have parent table

create table parent ( pid int not null, name varchar(255) )

and a parent-child join table

How can I find all parent's names where all their children have names in the following list ('dave','henry','myriam','jill').

I don't want to see a parent if they have a child with a different name, but if they have 1 or more children and all their children have names in the list I want to see the parent's name.

I did find this https://stackoverflow.com/a/304314/1916621 that will help me find a parent with children of the exactly those names, but I can't figure out how to the parents who only have children with names in a subset of that list.

Extra points if someone knows performance tradeoff for different approaches.

最满意答案

SELECT p.pid, p.name FROM parent p WHERE NOT EXISTS ( SELECT * FROM parent_child pc JOIN child c ON pc.cid = c.cid AND c.name NOT IN ('dave','henry','myriam','jill') WHERE p.pid = pc.pid ) AND EXISTS ( SELECT * FROM parent_child pc JOIN child c ON pc.cid = c.cid AND c.name IN ('dave','henry','myriam','jill') WHERE p.pid = pc.pid )

另一种方法......没有子查询，但需要额外的DISTINCT来消除parent记录的重复加入parent_child表。

SELECT DISTINCT p.pid, p.name FROM parent p JOIN parent_child pc_exists ON pc_exists.pid = p.pid JOIN child c_exists ON c_exists.cid = pc_exists.cid AND c_exists.name IN ('dave','henry','myriam','jill') LEFT JOIN parent_child pc_notExists ON pc_notExists.pid = p.pid LEFT JOIN child c_notExists ON c_notExists.cid = pc_notExists.cid AND c_notExists.name NOT IN ('dave','henry','myriam','jill') WHERE c_notExists.cid IS NULL SELECT p.pid, p.name FROM parent p WHERE NOT EXISTS ( SELECT * FROM parent_child pc JOIN child c ON pc.cid = c.cid AND c.name NOT IN ('dave','henry','myriam','jill') WHERE p.pid = pc.pid ) AND EXISTS ( SELECT * FROM parent_child pc JOIN child c ON pc.cid = c.cid AND c.name IN ('dave','henry','myriam','jill') WHERE p.pid = pc.pid )

Another method... no sub-queries, but additional DISTINCT needed to eliminate duplication of parent records from joining to the parent_child table.

更多推荐