Determining unique children

Hi,

I need to be able to identify if a parent has the same child names as another parent.

Have a look at my data (in pic)

Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

So you would get something like:

Bob | 2
Bill | 1
Glen | 2

thanks,
david.That's some interesting homework!

CREATE TABLE #patp (
parent VARCHAR(20)
, child VARCHAR(20)
)

INSERT INTO #patp (parent, child)
SELECT 'Bob', 'Jen' UNION
SELECT 'Bob', 'Jill' UNION
SELECT 'Bob', 'Mike' UNION
SELECT 'Bill', 'David' UNION
SELECT 'Bill', 'Steve' UNION
SELECT 'Glen', 'Jen' UNION
SELECT 'Glen', 'Jill' UNION
SELECT 'Glen', 'Mike'

SELECT DISTINCT a.parent
, (SELECT Count(*)
FROM (SELECT b.parent AS p1, c.parent AS p2
FROM #patp AS b
FULL JOIN #patp AS c
ON (c.child = b.child)
WHERE c.parent = a.parent
GROUP BY b.parent, c.parent
HAVING Count(*) = Count(b.parent)
AND Count(*) = Count(c.parent)) AS z)
FROM #patp AS a

DROP TABLE #patp
-PatP|||OK now, David...tell us why that works,now.

and show your work!!! ;)|||C'mon Pat. This is not the first time this guy has posted his homework for someone else to do. Reviewing his code for errors is one thing, but writing code for him is helping him cheat.|||let's hope he turns it in using the #patp table name, at least ;)

...and not even a "thank you, you can have half of my class credits"|||but writing code for him is helping him cheat.Only if he's incredibly gutsy and not very bright. I think he's smarter than that.

...and not even a "thank you, you can have half of my class credits"Why only half? If he retypes it for presentation, I'd give him 20%, but that's about all.

As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

-PatP|||As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

I'd be hard pressed....

working on it|||thanks for the code Pat!

I'm not a student, and that's not a homework assignment. (Been a while since then). I'm actually going to be using it (obviously re-worked to my own data) as an intergrity check for data Im pulling from a .xls file manually entered by a business user.

I was going to end up creating a function, but I knew someone here would have the guru-ishness to do it with a straight SELECT...obviously advantageous.

For simplicity I provided an example that I suppose looked like a homework assignment...LOL...the real scenario just would have been really annoying and difficult to explain. (and would have been one of those posts that people skip over)

as for explaining the code...cripes...i thought my SQL was pretty good. Lots more to learn. Although, I will be able to re-use what you provided Pat.

cheers.