Skip to content
Advertisement

How find row by exact much of joined rows with SQL?

I have two tables

parent:
   ╔════╦══════╗
   ║ id ║ name ║
   ╠════╬══════╣
   ║ 1  ║ A    ║
   ║ 2  ║ B    ║
   ║ 3  ║ A    ║
   ║ 4  ║ A    ║
   ║ 5  ║ A    ║
   ╚════╩══════╝
child:
   ╔════╦═══════════╦══════╗
   ║ id ║ parent_id ║ name ║
   ╠════╬═══════════╬══════╣
   ║ 1  ║     1     ║  a   ║
   ║ 2  ║     1     ║  b   ║
   ║ 3  ║     2     ║  a   ║
   ║ 4  ║     2     ║  b   ║
   ║ 5  ║     3     ║  a   ║
   ║ 6  ║     4     ║  a   ║
   ║ 7  ║     4     ║  c   ║
   ║ 8  ║     5     ║  a   ║
   ║ 9  ║     5     ║  b   ║
   ║ 10 ║     5     ║  c   ║
   ╚════╩═══════════╩══════╝

I want to find specific row from parent table with name A which have exact two related rows in child table with names a and b. In example table is only one row is suitable (with id = 1).

Row with id = 2 is not suitable because name of parent is B. Row with id = 3 is not suitable because it has only one child. Row with id = 4 is not suitable because one of childs has name c. Row with id = 5 is not suitable because it has one unnecessary child with name c.

Help me please write SQL query to find row from parent table with any number of specified childs. Thank you

Advertisement

Answer

Join the tables, group by the id of the parent and use group_concat() (for MySql or an equivalent function for other databases) in the HAVING clause:

select p.id
from parent p join child c
on c.parent_id = p.id
group by p.id
having group_concat(c.name order by c.name) = 'a,b';

I assume there are no duplicate combinations of (parent_id, name) in the table child. If there are then change to:

having group_concat(distinct c.name order by c.name) = 'a,b';

You will have to provide a sorted comma separated list like 'a,b' to specify the children that you search for.

8 People found this is helpful
Advertisement