if you have parent table
create table parent (
pid int not null,
name varchar(255)
)
and a parent-child join table
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)
)
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
)
Another method... no sub-queries, but additional DISTINCT
needed to eliminate duplication of parent
records from joining to the parent_child
table.
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