CODEWITHSUNDEEP
sqljoin
Sardoan
Sardoan

Reputation: 817

SQL get Parent where Children have specific values

Given is a Parent with the field id and Child relation with parent_id and name. How would a query look like to get all Parents which have two children, one with the name 'John' and one with the name 'Mike'. My problem is, that I am not able to build a query which returns the Parents having both children. I used Where IN ('John', 'Mike') so I get also the Parents returned which have also one child with the name 'John' or 'Mike'. But I want only the Parents with both children only.

SELECT * FROM Parent
JOIN Child ON Child.parent_id = Parent.id
WHERE Child.name IN ('John', 'Mike')

My query is of course more complex and this is only an abstraction for what I want to achieve. I have in mind, that I first need to join the children on parent_id and make something with that, but no idea.

Upvotes: 2

Views: 2908

Answers (4)

Sardoan
Sardoan

Reputation: 817

So, added the solution with the double join into an Ecto query and it passed my tests :)

from c in Child,
join: p in Parent, on: c.parent_id = p.id,
join: cc in Child, on: p.id = cc.parent_id,
where: c.name == ^"John",
where: cc.name == ^"Mike"
select: count(p.id)

Thanks for the ideas and the fast help :)

Upvotes: 0

David Chan
David Chan

Reputation: 7505

something like this would work in postgres if you have having.

SELECT parent_id, SUM(num) FROM (
  SELECT parent_id, 1 as num FROM Child Where name = 'John'
  UNION
  SELECT parent_id, 1 as num FROM Child Where name = 'Mike'
) parents 
GROUP BY parent_id HAVING SUM(num) = 2

Upvotes: 0

dfundako
dfundako

Reputation: 8314

You can do two joins and look for your specific records. This example shows that parent 1 will return with both kiddos, but not parent 2 that only has a Mike.

DECLARE @parent TABLE (ID INT)
DECLARE @child TABLE (ID INT, parentID INT, name VARCHAR(100))

INSERT INTO @parent 
VALUES 
(1),
(2),
(3),
(4),
(5),
(6)

INSERT INTO @child (ID, parentID, name)
VALUES 
(1, 1, 'Mike'),
(2, 1, 'John'),
(3, 2, 'Mike'),
(4, 2, 'Bill'),
(5, 3, 'Dave'),
(6, 4, 'Sam')

SELECT p.*

FROM @parent p

INNER JOIN @child c1
    ON c1.parentID = p.id
    AND c1.name = 'Mike'

INNER JOIN @child c2
    ON c2.parentID = p.ID
    AND c2.name = 'John'

Upvotes: 2

JoeWilson
JoeWilson

Reputation: 166

Try having two steps in the where clause. Both conditions will have to be true to return a parent record. 

where parent.id in (select parent_id from child where child.name='John')
and parent.id in (select parent_id from child where child.name='Mike')

Upvotes: 1

Related Questions