DCJones DCJones - 3 months ago 9
MySQL Question

MySQL LEFT OUTER JOIN to include third table

I am trying to write a query that uses a LEFT OUTER JOIN on three tables. I have complete the first part to join two tables but I am stuck on intergarting the third table.

What I need is the "Status" field for the NXLHR_Valid to be included in the first query.

Below are my to queries, how would I include the SECOND query into the FIRST query

FIRST QUERY

SELECT NXLHR_SequenceNo_default.SeqNo, NXLHR_SequenceNo_default.SeqHeader, NXLHR_SequenceNo_default.SeqText, NXLHR_Hist.UniqueID, NXLHR_Hist.Room, NXLHR_Hist.Status, NXLHR_Hist.Water, NXLHR_Hist.AuditBy
FROM NXLHR_SequenceNo_default
LEFT OUTER JOIN NXLHR_Hist
ON NXLHR_SequenceNo_default.SeqID = NXLHR_Hist.SeqID
AND NXLHR_Hist.UniqueID = 'NXLHR01031472477564'
WHERE NXLHR_SequenceNo_default.SeqActive = 1
ORDER BY NXLHR_SequenceNo_default.OrderID


SECOND QUERY

SELECT NXLHR_Valid.UniqueID, NXLHR_Valid.Status
FROM NXLHR_Valid
WHERE NXLHR_Valid.UniqueID = 'NXLHR01031472477564'


Any help would be great. Thank you for your time.

Answer
SELECT d.SeqNo
     , d.SeqHeader
     , d.SeqText
     , h.UniqueID
     , h.Room
     , h.Status
     , h.Water
     , h.AuditBy 
     , v.Status 
  FROM NXLHR_SequenceNo_default d
  LEFT 
  JOIN NXLHR_Hist h
    ON h.SeqID = d.SeqID 
   AND h.UniqueID = 'NXLHR01031472477564' 
  LEFT
  JOIN NXLHR_Valid v
    ON v.UniqueID = h.UniqueID  
 WHERE d.SeqActive = 1
 ORDER 
    BY d.OrderID