MS SQL

MS SQL RSS

Eliminating Duplicates Help please

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2004

Posts: 217

Reputation: marceta is an unknown quantity at this point

Solved Threads: 0

marceta

Offline

Posting Whiz in Training

Eliminating Duplicates Help please

Aug 29th, 2007

Hi guys,

The question I need to answer is

•

This query is about patients who got the same illness on the same day. For each pair of such patients, display one row showing the illness code, the date of onset, and the patient number of each person involved, e.g.
FLU 2007-08-21 1021 1024

Do not show the same pair of patients more than once, i.e. with the patient numbers swapped around, e.g. don’t show:
FLU 2007-08-21 1021 1024
FLU 2007-08-21 1024 1021

I have done so successfully...but I can not for the life of me work out how to not show the pair more than once!!! I have tried to add distinct patient numbers as that is all that my SQL skills can do, but they will still show duplicates! Here is my code

With Joins

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT I1.IllnessCode, I1.DateOfOnset, P1.PatientNum, P2.PatientNum
FROM Patients P1, Patients P2, Illhealth I1, IllHealth I2
WHERE P1.PatientNum = I1.PatientNum
AND P2.PatientNum = I2.PatientNum
AND I1.DateofOnSet = I2.DateofOnset
AND I1.IllnessCode = I2.IllnessCode
AND P1.PatientNum NOT LIKE P2.PatientNum
SELECT I1.IllnessCode, I1.DateOfOnset, P1.PatientNum, P2.PatientNum
FROM Patients P1, Patients P2, Illhealth I1, IllHealth I2
WHERE P1.PatientNum = I1.PatientNum
AND P2.PatientNum = I2.PatientNum
AND I1.DateofOnSet = I2.DateofOnset
AND I1.IllnessCode = I2.IllnessCode
AND P1.PatientNum NOT LIKE P2.PatientNum

With Subquery and Joins!

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT I1.IllnessCode, I1.DateOfOnset, P1.PatientNum, P2.PatientNum
FROM Patients P1, Patients P2, Illhealth I1
WHERE I1.IllnessCode IN (
	SELECT I1.IllnessCode
	FROM IllHealth I2
	WHERE P1.PatientNum = I1.PatientNum
	AND P2.PatientNum = I2.PatientNum
	AND I1.DateofOnSet = I2.DateofOnset
	AND I1.IllnessCode = I2.IllnessCode
	AND P1.PatientNum NOT LIKE P2.PatientNum)
SELECT I1.IllnessCode, I1.DateOfOnset, P1.PatientNum, P2.PatientNum
FROM Patients P1, Patients P2, Illhealth I1
WHERE I1.IllnessCode in (
	SELECT I1.IllnessCode
	FROM IllHealth I2
	WHERE P1.PatientNum = I1.PatientNum
	AND P2.PatientNum = I2.PatientNum
	AND I1.DateofOnSet = I2.DateofOnset
	AND I1.IllnessCode = I2.IllnessCode
	AND P1.PatientNum NOT LIKE P2.PatientNum)

Here is the output. Same for each query (just differnt orders). I know its right as I have manually checked, but cant get those darn duplicates out!!

http://www.imagehosting.com/out.php/i1079014_output.jpg ([ img] tags not working?)

Can someone please help me out

Thanks in advance.

Pete

PS.Here is the db layout in case you need it.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
Patients (PatientNum, GivenName, FamilyName, Gender, Address, DateOfBirth, DateAdmitted, WardId)
Illhealth (PatientNum, IllnessCode, DateOfOnset, DiagnosedByStaffNum)
Illnesses (IllnessCode, IllnessDescription, TypicalDuration)
Staff (StaffNum, GivenName, FamilyName, MedicalRole, WorkPhoneNum, HomePhoneNum)
Wards (WardId, WardName)
Roster (StaffNum, WeekNum, DayNum, ShiftNum, WardId, InCharge)
Patients (PatientNum, GivenName, FamilyName, Gender, Address, DateOfBirth, DateAdmitted, WardId)
Illhealth (PatientNum, IllnessCode, DateOfOnset, DiagnosedByStaffNum)
Illnesses (IllnessCode, IllnessDescription, TypicalDuration)
Staff (StaffNum, GivenName, FamilyName, MedicalRole, WorkPhoneNum, HomePhoneNum)
Wards (WardId, WardName)
Roster (StaffNum, WeekNum, DayNum, ShiftNum, WardId, InCharge)