Geek Answers Handbook

SQL select parent with children only in

if you have a parent table

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

and table with parent and child join

 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 to find all parent names where all their children have names in the following list ("dave", "henry", "myriam", "jill").

I do not want to see the 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 name.

I have found this https://stackoverflow.com/a/167168/ which helps me find a parent with children of exactly these names, but I cannot understand how parents who have only children with names in a subset of this list.

Extra points if someone knows the performance tradeoff for different approaches.

+4
source share
2 answers
 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 subqueries except the extra DISTINCT needed to eliminate duplicate parent entries from joining 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
+3
source

Here is my moderate rate:

Examples of 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

Using REGEXP and GROUP_CONCAT

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

* But the problem is this: this string order group_concat should be found in a comma-separated string. * If we do not make REGEXP very effective :)

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

+1
source

More articles:


All Articles