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

Member 15627495 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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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