CODEWITHSUNDEEP
sqljoin
Martijn Hiemstra
Martijn Hiemstra

Reputation: 1170

SQL: How to find all parents where parents have children with given id

I have a question about a parent / child join table. I have a join table called parent_children. The table contains a parent_id and child_id. So like this:

parent_id  | child_id
1            1
1            2
1            3
2            1
2            3
3            1
3            4

What I want is to find all parents who have children in a certain list. So let's say the list contains 1 and 3 then I want parent_id 1 and 2. If the list contains 4 I want parent_id 3. How do I do this?

Upvotes: 0

Views: 405

Answers (2)

George Joseph
George Joseph

Reputation: 5922

So you basically need to count the number of occurrences in the IN list of your input. The following can help.

create table parent_children(parent_id int, child_id int)


insert into parent_children values(1,1)
insert into parent_children values(1,2)
insert into parent_children values(1,3)
insert into parent_children values(2,1)
insert into parent_children values(2,3)
insert into parent_children values(3,1)
insert into parent_children values(3,4)



with list_data
  as(select *
       from (values(1),(3))as t(x)
    )
   select a.x          
     from list_data a
left join parent_children b
       on a.x=b.child_id
  group by a.x
having count(*) = count(distinct b.parent_id)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use group by, where, and having:

select parent_id
from t
where child_id in (1, 3)
group by parent_id
having count(*) = 2;  -- "2" is the number of items in the list

This assumes no duplicate parent/child rows in the table. If this is possible, then use count(distinct child_id) = 2.

Upvotes: 2

Related Questions