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!!
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.
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)
Offline 
Unsolved 
