Select parents for a list of interesting properties for parent children.

Question

I struggled with the title, but let me explain:

Let's say I have two data structures: Parentand Child. In my (Scala) encoder, each instance Parenthas a list Child. I have two tables in the database, one for Parentand one for Child. Each entry in the table Childhas a value parentIdthat points to it Parent.

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

Given the list of identifiers Child, I want to choose each Parent(of which there can be no one, not one) in which there are all these children. Can someone help me with a request?

UPDATE:

My example did not cover my use case - sorry. I need to add another field to Child: Let me name 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)
);

I need to find parents who have 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 need a query in which these examples work:

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

Answer 1

ARRAY_AGG interestingThing parent.id @> ():

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)

Answer 2

-

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

, , , .