Query database that does not use a stored procedure

i have a db diagram as shown above. What I need to do is: Select a table which is as follows: PatientId, Emergency.Name or Doctor Names, Patient.Address.

Explanation; I need A query which will return patient details and, if Patient has an emergency also add an emergency, if not select all doctor.Name into one row.

So, example:

So, the first row was built because EmergencyId in table patient was null, while the second row had an emergency Id.

I need the query to simulate this. Using SSRS

Thanks a lot!

---

Thanks guys, can you at least explain me how to return this data in separate rows, so I can union later on?

I believe this will return the data you want broken out into separate rows, returning Emergency if it exists and the Drs if it does not. Good luck!

SELECT Distinct Coalesce(E.Name, D.Name) as VariableColumn,
       P.PatientId,
       P.Address
FROM Patient P
  LEFT JOIN Emergency E
    ON P.EmergencyId=E.EmergencyId
  LEFT JOIN PatientDoctor PD
    ON P.PatientID=PD.PatientId
  LEFT JOIN Doctor D
    ON PD.DoctorId=D.DoctorId