Avatar of AkAlan
AkAlan
 asked on

How to return only those rows in a related table where there are more than one related row

I have two tables that are related. I would like to write a query that returns only those rows that have more than one related record in the child table.

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
edlunad

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
kaufmed

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
edlunad

you can try something like this...

SELECT     a.field1, COUNT(*) AS Cnt
FROM         ParentTable AS a INNER JOIN
                      ChildTable AS b ON a.field1 = b.field1
GROUP BY a.field1
HAVING      (COUNT(*) > 1)

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes