sqlpostgresqlscalikejdbc

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

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:

Solution

  • 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)