SQL query over parents with exact same set of children

Question

I have parent-child relation in my table (simplified version listed below):

| parent | child |
|--------+-------|
| p1     | c1    |
| p1     | c2    |
|--------+-------|
| p2     | c1    |
| p2     | c2    |
|--------+-------|
| p3     | c1    |
|--------+-------|
| p4     | c2    |
|--------+-------|
| p5     | c3    |

I need to query all parents with the exact same set of children like p1 and p2.

Seems to be related to this question, but there are hard-coded children.

Sounds like you'll need a recursive CTE to build up a structure which aggregates children - and then you can group by the children. — Allan S. Hansen, Mar 20 '14 at 06:35
that means you wanted to fetch all the parents having same set of childs. — Divya, Mar 20 '14 at 07:34

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

This query lists all parents sharing the same children, where there are more than one parent:

SELECT array_agg(parent) AS parents, children, count(*) AS dupes
FROM (
   SELECT parent, array_agg(child ORDER BY child) AS children
   FROM   tbl
   GROUP  BY 1
   ) sub
GROUP  BY 2
HAVING count(*) > 1;

Simple, but not the fastest possible solution. For big tables, I would try something else.

The problem belongs to the "relational division" family. Here is a collection of query styles we assembled some time ago:
How to filter SQL results in a has-many-through relation

One way (of many) to get single parent per row:

WITH cte AS (
   SELECT parent, array_agg(child ORDER BY child) AS children
   FROM   tbl
   GROUP  BY 1
   )
SELECT *
FROM   cte c
WHERE EXISTS (
   SELECT 1
   FROM   cte c1
   WHERE  c1.children = c.children
   AND    c1.parent <> c.parent
   );

edited May 23 '17 at 12:06

Community

1
1

answered Mar 20 '14 at 07:39

Erwin Brandstetter

605,456
145
1,078
1,228

Great! But how can I eliminate multiple results per cell like `{p1,p2}`? – ilardm Mar 20 '14 at 08:08
@ilardm: I think I answered the given question as good as possible. There is nothing about eliminating anything in the question. I suggest you ask a *new question* with your new (precise) requirements. – Erwin Brandstetter Mar 20 '14 at 08:26
Thank you for your answer! I'll accept it as soon as `parents` column could be usable for further queries (please refer [comment](http://stackoverflow.com/questions/22525073/sql-query-over-parents-with-exact-same-set-of-children#comment34277620_22525073)) – ilardm Mar 20 '14 at 08:53
@ilardm: Added a single parent version. Essential information should be in the question, not scattered in comments. Use `edit` to improve (not change) your question. – Erwin Brandstetter Mar 20 '14 at 09:04

SQL query over parents with exact same set of children

1 Answers1