Posted by Erland Sommarskog on 10/01/36 11:19

RichMUK (support@insurance.uk.com) writes:
> MCs query works fine but I need to report just one record per family.
> In this example the tables are:
>
> Family.FamilyID [id]
> Family.FamilyName
> Family.Address
>
> and
>
> FamilyMembers.FamilyMemberID [id]
> FamilyMembers.FamilyID
> FamilyMembers.FirstName
> FamilyMembers.Age
>
> SELECT f.FamilyID, f.FamilyName, f.Address
> FROM dbo.Family f INNER JOIN
> dbo.FamilyMembers fm ON f.FamilyID = fm.FamilyID
> WHERE (fm.Age > 16)
>
> MC, the query you suggest repeats the same Family. record by the number
> of FamilyMembers in the query.

SELECT f.FamilyID, f.FamilyName, f.Address
FROM dbo.Family f
WHERE EXISTS (SELECT *
FROM dbo.FamilyMembers fm
WHERE f.FamilyID = fm.FamilyID
AND fm.Age > 16)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]