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.