SQL 2008 exclude parent records in results where multiple child records exist in 2nd table

[NickyJay]

Hi all,

hope someone can help! I have a query to look for duplicate records in our database. my duplicate query looks for any records that match on foename/surname/dob and ouptuts address details from the main table, then pulls out an extra reference from a 2nd table. My problem is that if a client has 2 or more child records in the 2nd table, the main record is coming out in the duplicate report, yet the record is not really a duplicate. I need to output any child records where they exist as they relate to who the client in the parent record is dealing with. What i need to do is prevent any parent records that have multiple child records from being returned UNLESS they do have a duplicate parent record in the main table. Sorry if im not making much sense/waffling...
here's my query:

SELECT C.Client_Ref, C.Surname, C.NameFirst, C.DOB, C2.Client_Ref AS DupClientRef, C2.Surname AS DupClientSurname, C2.NameFirst AS DupClientNameFirst, C2.DOB AS DupClientDOB, C.AddSubDwelling, C.AddDwelling, C.AddStreet, C.AddLocality, C.AddTown, C.Postcode, R.TextRef
FROM C2 RIGHT OUTER JOIN
               R RIGHT OUTER JOIN
               C ON R.Client_Ref = C.Client_Ref ON C2.DOB = C.DOB AND 
               C2.NameFirst = C.NameFirst AND C2.Surname = C.Surname
WHERE C.DOB = C2_1.DOB
AND C.NameFirst = C2.NameFirst 
AND C.Surname = C2.Surname
AND C.Client_Ref <> C2.Client_Ref 
AND C.DateForDelete IS NULL
ORDER BY C.Surname, C.NameFirst, C.DOB

In the query my main table is C, the duplicate is C2 and the child table is R

Many thanks

 

[JarlH]

First, if you really want OUTER JOIN's instead of regular INNER JOIN's, put the join conditions in the ON clauses, not in the where clause. And also put the "outer" tables conditions in the ON clause.

Personally, to make things easier to read and to understand, I'd turn the query around and use LEFT OUTER joins. (RIGHT OUTER joins are too backward thinking to me.) Just a matter of taste...

[tt]
SELECT C.Client_Ref, C.Surname, C.NameFirst, C.DOB, C2.Client_Ref AS DupClientRef, C2.Surname AS DupClientSurname, C2.NameFirst AS DupClientNameFirst, C2.DOB AS DupClientDOB, C.AddSubDwelling, C.AddDwelling, C.AddStreet, C.AddLocality, C.AddTown, C.Postcode, R.TextRef
FROM C LEFT OUTER JOIN R
ON R.Client_Ref = C.Client_Ref
LEFT OUTER JOIN C2
ON C2.DOB = C.DOB
AND C2.NameFirst = C.NameFirst
AND C2.Surname = C.Surname
AND C.DOB = C2_1.DOB
AND C.NameFirst = C2.NameFirst
AND C.Surname = C2.Surname
AND C.Client_Ref <> C2.Client_Ref
WHERE C.DateForDelete IS NULL
ORDER BY C.Surname, C.NameFirst, C.DOB [/tt]

If you give us the create table statements together with some sample data and the wanted output it'd be much easier to help you!

 

[NickyJay]

Hiya,
the code has ben auto written in SSRS 2008, so where i had originally put left outer joins it seems to have swopped for right outer?!?!?? didn't spot that until you've mentioned it. As for the create table stmt, i didn't create it and its on a hosted database that i just write reports to, sorry if that makes it harder to solve - it really does for me at times as im so limited on what i can and can't do!!

sample data example would be:
id Name DOB postcode ref(from 2nd table)
124 John 23/09/2001 S64 8ed 2387653
543 John 23/09/2001 S64 8ed 2387653
126 james 12/05/1999 s54 8yy A55N001458
126 james 12/05/1999 null 20014
154 kate 07/02/1996 ed5 6tt null
433 kate 07/02/1996 ed5 6tt null

so in my example, both john and kate should show as they are duplicate records with different ID numbers, yet james shouldn't show as it is the same record but displaying both of his child records from the 2nd table (ref column)
Hope this helps explain it better!
TIA
nicola

NickyJay Designs - for quality canvas images from your photos.

http://www.nickyjaydesigns.co.uk

 

[JarlH]

Check out GROUP BY in combination with HAVING and COUNT(DISTINCT)!

To find persons with two (or more) different id values:
[tt]
SELECT NameFirst, Surname, DOB
FROM C
GROUP BY NameFirst, Surname, DOB
HAVING COUNT(DISTINCT id) > 1
[/tt]

The easiest way to get the result you're looking for is to add this query to your where-clause, something like:
[tt]
SELECT C.Client_Ref, C.Surname, C.NameFirst, C.DOB, C2.Client_Ref AS DupClientRef, C2.Surname AS DupClientSurname, C2.NameFirst AS DupClientNameFirst, C2.DOB AS DupClientDOB, C.AddSubDwelling, C.AddDwelling, C.AddStreet, C.AddLocality, C.AddTown, C.Postcode, R.TextRef
FROM C LEFT OUTER JOIN R
ON R.Client_Ref = C.Client_Ref
LEFT OUTER JOIN C2
ON C2.DOB = C.DOB
AND C2.NameFirst = C.NameFirst
AND C2.Surname = C.Surname
AND C.DOB = C2_1.DOB
AND C.NameFirst = C2.NameFirst
AND C.Surname = C2.Surname
AND C.Client_Ref <> C2.Client_Ref
WHERE C.DateForDelete IS NULL
AND EXISTS (SELECT 1 FROM C as C_SQ
WHERE C_SQ.NameFirst = C.NameFirst
AND C_SQ.Surnam = C.Surnam
AND C_SQ.DOB = C.DOB
GROUP BY NameFirst, Surname, DOB
HAVING COUNT(DISTINCT id) > 1)
ORDER BY C.Surname, C.NameFirst, C.DOB
[/tt]

 

[PHV]

What about this ?

SELECT A.Client_Ref,A.Surname,A.NameFirst,A.DOB,A.AddSubDwelling,A.AddDwelling,A.AddStreet,A.AddLocality,A.AddTown,A.Postcode,B.TextRef
FROM [i]mainTable[/i] A INNER JOIN (
SELECT Surname,NameFirst,DOB FROM [i]mainTable[/i] WHERE DateForDelete IS NULL GROUP BY Surname,NameFirst,DOB HAVING COUNT(*)>1
) D ON A.Surname=D.Surname AND A.NameFirst=D.NameFirst AND A.DOB=D.DOB
LEFT JOIN [i]secondTable[/i] B ON A.Client_Ref=B.Client_Ref
WHERE A.DateForDelete IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886