Select parents for a list of interesting properties in the parent's children

Question

I struggled with the title, but let me explain:

Let's say I have two data structures: Parent and Child. In my (Scala) code, each Parent instance has a list of Child's. In the database I have two tables, one for Parent and one for Child. Each entry in the Child table has a value parentId that points to its Parent.

Table for Parent: id int
Table for Child: id int, parentId int (foreign key parent.id)

Given a list of Child IDs, I want to select each Parent (of which there can be none, one or many) that has all these children. Can someone help me out with the query?

UPDATE:

My example didn't cover my use case - sorry. I need to add another field in Child: Let's call it interestingThing. Here are the tables:

CREATE TABLE Parent (
  id                INT PRIMARY KEY
);
CREATE TABLE Child (
  id                INT PRIMARY KEY,
  interestingThing  INT,
  parentId          INT,
  FOREIGN KEY (parentId) REFERENCES Parent (id)
);

What I need is to find parents that has children with my list of interesting things. Given this data:

INSERT INTO Parent VALUES (1);
INSERT INTO Parent VALUES (2);

INSERT INTO Child VALUES (1, 42, 1);
INSERT INTO Child VALUES (2, 43, 1);
INSERT INTO Child VALUES (3, 44, 1);
INSERT INTO Child VALUES (4, 8, 2);
INSERT INTO Child VALUES (5, 9, 2);
INSERT INTO Child VALUES (6, 10, 2);
INSERT INTO Child VALUES (7, 8, 1);

I want a query that gets these examples working:

Given the interesting things (42, 43), I would like to find the parent with id 1.
Given the interesting things (43, 44), I would like to find the parent with id 1.
Given the interesting things (8), I would like to find the parent with id 1 and the parent with id 2.
Given the interesting things (8, 10), I would like to find the parent with id 2.

Add some sample table data and the expected result - as well formatted text. Also show us your current query attempt. And tag the dbms you're using. — jarlh, Apr 12 '17 at 11:16

score 2 · Accepted Answer · answered Apr 12 '17 at 11:59

You could use the ARRAY_AGG function to get an array of all the interestingThing for a parent.id and use the @> (contains) operator:

SELECT p.id 
FROM parent p 
INNER JOIN child c 
  ON p.id = c.parentId
GROUP BY p.id 
HAVING ARRAY_AGG(interestingThing) @> '{8}';

┌────┐
│ id │
├────┤
│  1 │
│  2 │
└────┘
(2 rows)

SELECT p.id 
FROM parent p 
INNER JOIN child c 
  ON p.id = c.parentId
GROUP BY p.id 
HAVING ARRAY_AGG(interestingThing) @> '{8,10}';

┌────┐
│ id │
├────┤
│  2 │
└────┘
(1 row)

score 0 · Answer 2 · answered Apr 12 '17 at 11:17

0

You can do that with something like this

select  parentId
from    Child
where   id in ( /* your list */ )
group by parentId
having  count(distinct id) = /* your list's length */

You will get only the parents that have a count of children equal to the length of your list, only taking into account the desired children and each one only once.

answered Apr 12 '17 at 11:17

Stefano Zanini

5,876
2
13
33

Thank you for answering! Do you mean `where parentId in (my list)` there? Also, How can I modify this so that it allows the parent to have more children that are not in my list? – L42 Apr 12 '17 at 11:22
No, I mean what I wrote :) I'll think about a solution for the change you describe – Stefano Zanini Apr 12 '17 at 11:24
Thanks. See my updated question though - my first question didn't cover my use case. – L42 Apr 12 '17 at 11:44

Select parents for a list of interesting properties in the parent's children

2 Answers2