Question 20546 in Mysql Question posted in
The official MySQL documentation can be found here.

How to I fetch parent records with the same set of child records from a single table – Mysql

I want to write a SQL query that will find all the number of records in a table that are associated with the same child records.

For example:

In the dataset below parents 1001 and 1003 are associated with the same child records:

Parent ID Child ID
1001 C101
1001 C102
1001 C103
1002 C201
1002 C202
1002 C203
1003 C101
1003 C102
1003 C103

Expected Results

I want to see for each parent the number of other parent records that are associated with the same set of child records, like this:

Parent ID Total with same child records
1001 1
1002 0
1003 1

I have tried various joins but unable to figure out a sensible solution. Ideas please, thanks.

Tags:

2

Answers


  1. 0

    For each parent, you want to build a list of children ids. In Oracle this would be the LISTAGG() function, but in MySQL it seems to be GROUP_CONCAT() (see Aggregate function in MySQL – list (like LISTAGG in Oracle) ). So something like

    select ParentID, GROUP_CONCAT(ChildID separator ',') from ...

    Once you have that, you can analyze it in a spreadsheet, or add a GROUP BY to count how many parents have the same set of children

    Login or Signup to reply.
  2. 0

    You could use the count window function and group_concat function as the following:

    select ParentID, 
  count(*) over (partition by group_concat(ChildID order by ChildID)) -1 as 'Total with same child records'
from tbl_name
group by ParentID
order by ParentID

    see demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search