SQL select a parent with children only in

Question

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.

Answer 1

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

Answer 2

Here is my moderate bet:

Sample tables:

Parent

PID     NAME
1       dad john
2       mum sandy
3       dad frank
4       mum kate
5       mum jean

Child

CID     NAME
11      dave
22      maryam
33      henry
44      maryam
16      jill
17      lina
23      jack
34      jill
55      dave

Parent_Child

PID     CID
1       11
1       16
1       17
2       22
3       33
4       44
2       23
5       55
3       34

Query:

select p.pid, p.name, 
group_concat(c.name) as children
from parent as p
inner join parent_child as pc
on p.pid = pc.pid
join child as c
on pc.cid = c.cid
where c.name
in ('dave','henry','maryam','jill')
group by p.pid
;

Results:

PID     NAME        CHILDREN
1       dad john    dave,jill
2       mum sandy   maryam
3       dad frank   henry,jill
4       mum kate    maryam
5       mum jean    dave

• SQLFIDDLE

---

Using REGEXP and GROUP_CONCAT

It is something much better than in or find_in_set for SQL. The change I did, I used the list as a comma delimitted string ;)

*But the issue here: that group_concat string order has to be found in the comman delimitted string.* Unless we make the REGEXP much efficient :)

Query:

select x.pid, x.name,
x.children from(
select p.pid, p.name, 
group_concat(c.name) as children,
  count(c.name) as counts
from parent as p
inner join parent_child as pc
on p.pid = pc.pid
join child as c
on pc.cid = c.cid
group by p.pid) as x
where 'dave,maryam,henry,jill'
REGEXP x.children
;

Results:

PID     NAME        CHILDREN
3       dad frank   henry,jill
4       mum kate    maryam
5       mum jean    dave

*SQLFIDDLE