0

Hi,
I am having trouble with a question and am wondering if someone knows where I am going wrong.

I am to list the patients of a hospital (persons that have been admitted) that have a next of kin listed as a staff member. I am to list the patient's first name and surname and the staff members first name and surname.

3 tables:
admission
admission_id, patient_id
person
person_id, surname, first_name, Next_of_kin
staff
person_id

I have to answer using joins but I am unable to work it out. So far I have:

SELECT p1.First_name as [Patient First Name], p1.Surname  as [Patient Surname], p2.First_name as [Staff First Name], p2.Surname as [Staff Last Name]
FROM admission a JOIN staff st USING (Patient_id) JOIN person p1 USING (Person_id) JOIN person p2 USING (Person_id)
WHERE a.Patient_id=p1.Person_id and NOT (p1.Person_id=p2.Person_id) and p1.Next_of_kin=st.Person_id and p2.Person_id=st.Person_id;

I am able to figure it out using sub-queries:

SELECT p1.First_name as [Patient First Name], p1.Surname as [Patient Surname], p2.First_name as [Staff First Name], p2.Surname as [Staff Last Name]
FROM admission a, staff st, person p1, person p2
WHERE a.Patient_id=p1.Person_id and NOT (p1.Person_id=p2.Person_id) and p1.Next_of_kin=st.Person_id and p2.Person_id=st.Person_id;

However, I can't figure out how to work it out using Joins.

Any help will be greatly appreciated.

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by macca21
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.