Eliminating Duplicates Help please

[marceta]

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

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!

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

[img]http://www.imagehosting.com/out.php/i1079014_output.jpg[/img] ([ 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.

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)

[marceta]

P1.PatientNum < P2.PatientNum

*slaps head*!

geez cant belive i couldn't think of that!