Query database not using 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

---

I can't really debug or work with your tables to check this, but here is a rough stab at something that should get you going:

SELECT P.PatientId, E.Name as 'EmergencyName', D.Name as 'DoctorName', 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
ORDER BY P.PatientId, E.Name, D.Name, P.Address

Also, I'm no SQL Wizard. This should get the ball rolling, though.

• Can you test this query against your data?

• Does it return the desired results?

EDIT:

I just saw your comment about needing this layout with multiple values in one cell aligned vertically. That would require sub queries, and would be too much for me to write out.