×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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:
[code]
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
[/code]
In the query my main table is C, the duplicate is C2 and the child table is R

Many thanks :D

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

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...


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


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!

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

(OP)
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.

www.nickyjaydesigns.co.uk

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

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

To find persons with two (or more) different id values:

SELECT NameFirst, Surname, DOB
FROM C
GROUP BY NameFirst, Surname, DOB
HAVING COUNT(DISTINCT id) > 1


The easiest way to get the result you're looking for is to add this query to your where-clause, something like:

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

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

What about this ?

CODE --> SQL

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 mainTable A INNER JOIN ( SELECT Surname,NameFirst,DOB FROM mainTable 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 secondTable B ON A.Client_Ref=B.Client_Ref WHERE A.DateForDelete IS NULL

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close