Mysql – limiting rows in joins

join;MySQL

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).

Best Answer

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

Related Question