MAX state on left outer join

Question

---------- samples ---------- SamplesID stylenumber stylename status ----------- samples_details ----------- Samples_Details_ID SamplesID CustomerName date_out date_returned updated (timestamp) status ------------ samples_pictures ------------ SamplesPicID SamplesID 

Trying to write a query that gives me all rows from samples, all matches (if any) from samples_pictures and the most recent record (if any) from samples_details. Thus, there are two left outer joins, but one of them retrieves only the most recent record.

What does not work:

 SELECT samples.* , samples_pictures.SamplesPicID , CustomerName , date_out , date_returned , updated , samples_details.status as txn_status FROM samples LEFT OUTER JOIN ( SELECT Samples_Details_ID , samples_details.status as txn_status , MAX(updated) as MaxUpdated FROM samples_details GROUP BY Samples_Details_ID ) AS MaxTable ON MaxTable.SamplesID = samples.SamplesID LEFT OUTER JOIN samples_pictures ON samples.SamplesID = samples_pictures.SamplesID 

Any help would be greatly appreciated!

Answer 1

There is no SamplesID in your select statement for MaxTable . Must be:

 SELECT samples.*, samples_pictures.SamplesPicID, CustomerName, date_out, date_returned, updated, samples_details.status as txn_status FROM samples LEFT OUTER JOIN (SELECT SamplesID, Samples_Details_ID, samples_details.status as txn_status, MAX(updated) as MaxUpdated FROM samples_details GROUP BY Samples_Details_ID) AS MaxTable ON MaxTable.SamplesID = samples.SamplesID LEFT OUTER JOIN samples_pictures ON samples.SamplesID = samples_pictures.SamplesID 

Answer 2

In the original query, each record from samples_details is still selected.

You will need to create a subquery to filter out only those records with a maximum timestamp and join this table using the samples_details table to get additional columns.

 SELECT * FROM samples s LEFT OUTER JOIN sample_details sd ON sd.SamplesID = s.SamplesID INNER JOIN ( SELECT SamplesID , MAX(update) AS Update FROM samples_details GROUP BY SamplesID ) sdm ON sdm.SamplesID = sd.SamplesID LEFT OUTER JOIN samples_pictures sp ON sp.SamplesID = s.SamplesID 

Answer 3

 SELECT samples.* , samples_pictures.SamplesPicID , CustomerName , date_out , date_returned , updated , samples_details.status as txn_status FROM samples LEFT OUTER JOIN samples_details ON samples.SamplesID = samples_details.SamplesID AND samples_details.updated = ( SELECT MAX(updated) FROM samples_details WHERE SamplesID = samples.SamplesID) ) LEFT OUTER JOIN samples_pictures ON samples.SamplesID = samples_pictures.SamplesID 

Answer 4

 SELECT samples.* , samples_pictures.SamplesPicID , CustomerName , date_out , date_returned , updated , txn_status FROM samples LEFT OUTER JOIN ( SELECT Samples_ID , status as txn_status FROM samples_details INNER JOIN ( SELECT Samples_ID, Samples_Details_ID , MAX(updated) as MaxUpdated FROM samples_details GROUP BY Samples_ID, Samples_Details_ID ) AS MaxTable ON MaxTable.Samples_ID = samples.SamplesID AND MaxTable.Samples_Details_ID = samples.Samples_Details_ID ) AS MaxJoin LEFT OUTER JOIN samples_pictures ON samples.SamplesID = samples_pictures.SamplesID