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

Question

0.00/5 (No votes)

See more:

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

What I have tried:

Please help if you can. Thank you

Posted 6-Mar-23 23:00pm

SG8107

Updated 8-Mar-23 17:13pm

Add a Solution

Comments

[no name] 7-Mar-23 5:34am

in SQL, to 'avoid/isolate' a value from redundancy, use 'DISTINCT(the_column)'.

if you need to GROUP fields from a column, you have 'GROUP BY column'.

to apply maths function ( you need to count numbers of rows ) , you have COUNT(column) AS 'total'.
AS 'total' is an alias, that provide a new name for the column 'count(column)', the name of the column in your result will be 'total'.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-03-07T01:16:00

Solution 2

Or, a little simpler using GROUP BY and a JOIN:

SQL

SELECT DISTINCT a.PID
              , b.PIDCnt - 1 As [Total with same child records] 
  FROM Mytable a
JOIN (SELECT CID
           , COUNT(PID) As PIDCnt 
        FROM MyTable 
       GROUP BY CID) b
  ON a.CID = b.CID

Posted 7-Mar-23 1:16am

OriginalGriff

Comments

RickZeeland 7-Mar-23 7:45am

5d! Tested this on the [Sales].[CountryRegionCurrency] table of AdventureWorks db, and it works!
https://dbfiddle.uk/yYXzWUpp

OriginalGriff 7-Mar-23 8:18am

:D

Paul Maxwell 2023 · Answer 2 · 2023-03-08T17:13:00

The expected result can be achieved by a "self-join" through the ChildID column, like this:

select
       f.ParentID
     , count(distinct o.ParentID) as other_parents
from mytable as f
left join mytable as o on f.ChildID = o.childid 
                      and f.ParentId <> o.ParentID
group by
       f.ParentID
;

See http://sqlfiddle.com/#!18/08916/1"

This can be taken a little further by including string_agg (version dependent) so that you list out the "other" parents in a comma separated list:

select
       f.ParentID
     , count(distinct o.ParentID) as count_parents
     , STRING_AGG(o.ParentID,',') AS other_parents
from mytable as f
left join mytable as o on f.ChildID = o.childid 
                      and f.ParentId <> o.ParentID
group by
       f.ParentID
;

see: SQL Fiddle[^]

ParentID	count_parents   other_parents
1001			1			1003
1002			0			(null)
1003			1			1001

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

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

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

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us