General Help with a Query Needed

[Newby287]

OK, so I'm trying to get the admission id, name and ward name for patients who have been admitted but not operated on. The four tables used are PERSON, ADMISSION, OPERATION and WARD. Admission id and ward code are in admission, and Admission id is in admission and operation

What I have is:

SELECT ADMISSION.Admission_id, PERSON.First_name, PERSON.Surname, WARD.Ward_name
FROM ADMISSION, PERSON, WARD
Where ADMISSION.Patient_id = PERSON.Person_id
AND ADMISSION.Ward_code IS NOT NULL
AND ADMISSION_id NOT IN (SELECT ADMISSION_id FROM OPERATION);

This brings up six rows - the first two lines are correct, but then it gives additional lines for each of the two people for the other two ward names. I tried a nested select query to ispolate the Ward Codes for the two specific people but it didn't work.

Any ideas?

[Hack]

Do all four tables have a specific field that ties them altogether?

If not, they should, and you could then do a JOIN on those fields to further filter out your records.

[Newby287]

There is no one common thread throughout all four tables, but Admission_id is common in ADMISSION, PERSON and OPERATION and Ward_code is common in ADMISSION and WARD

[tkorsano]

Hi there,

According to what you have wrote, you might want to try this:

SELECT ADMISSION.Admission_id, PERSON.First_name, PERSON.Surname, WARD.Ward_name
FROM ADMISSION, PERSON, WARD
Where ADMISSION.Patient_id = PERSON.Person_id
AND ADMISSSION.ADMISSION_id = PERSON.ADMISSION_id
AND ADMISSION.Ward_code = WARD.Ward_code
AND ADMISSION.Ward_code IS NOT NULL
AND ADMISSION_id NOT IN (SELECT ADMISSION_id FROM OPERATION);

This might work. Otherwise, you only need to refine your where clause.

Best regards,

Tonci Korsano
"A supercomputer is to a computer, what Superman is to a man. Wake up and smell power!"