1

So I have this problem with one of my queries.

The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows

id | name | added
----------------------
1  | John | 2014-03-30
2  | Doe  | 2014-03-31

The patient_nurse table looks like this

id | patient_id | nurse_id | shift     | added
---------------------------------------------------
1  | 1          | 2        | MORNING   | 2014-03-30
2  | 1          | 3        | EVENING   | 2014-03-30
3  | 2          | 2        | AFTERNOON | 2014-03-31
4  | 2          | 1        | MORNING   | 2014-03-31
5  | 2          | 3        | EVENING   | 2014-03-31
6  | 2          | 4        | EVENING   | 2014-03-31

The nurses table looks like this

id | name
----------
1  | Jane
2  | Joe
3  | Will
4  | Harry

How should the query be if I want to get the following information?:

patient_name | nurse_primary | nurse_morning | nurse_afternoon | nurse_evening
------------------------------------------------------------------------------
John         | NULL          | Joe           | NULL            | Will
Doe          | NULL          | Jane          | Joe             | Will

As you can see, I just need 1 nurse data for 1 shift on a patient (why nurse Harry is left out), which is the nurse inserted first (lower id value).

2

Try this:

select 
 patients.name AS patient_name
,NULL As nurse_primary
,m.`name` As nurse_morning
,a.name As nurse_afternoon
,e.name As nurse_evening
FROM
patients left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='MORNING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1 
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='MORNING')) AS m 
ON m.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='AFTERNOON' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1 
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='AFTERNOON')) AS a
ON a.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='EVENING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1 
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='EVENING')) AS e
ON e.patient_id = patients.id

SQL Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.