Join Two SQL Tables To Get Latest Date

Kyle Witter
Kyle Witter used Ask the Experts™
I have two SQL tables i'm trying to join together, while presenting all of the data from one table (Material_Location), and only one field from the other table (Job.Order_Date).  Specifically, I'm trying to get the most recent order date from the Job Table for each line in the Material_Location Table.  Can someone please explain how to properly join them and limit the output?  Currently I get a new row of output for every date in the table for a given part.

SELECT        dbo.Material_Location.Material, dbo.Material_Location.Location_ID, dbo.Material_Location.On_Hand_Qty, dbo.Material_Location.Unit_Cost, dbo.Material_Location.Last_Updated
FROM            dbo.Material_Location LEFT OUTER JOIN
                         dbo.Job ON dbo.Material_Location.Material = dbo.Job.Part_Number

Open in new window

I've also attached a table of sample data showing my anticipated output.

Any assistance would be very much appreciated.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
I see at least 2 ways of doing it:

SELECT        ML.Material, ML.Location_ID, ML.On_Hand_Qty, ML.Unit_Cost, MAX(J.Order_Date) As Order_Date
FROM            dbo.Material_Location AS ML
ON ML.Material = J.Part_Number
GROUP BY ML.Material, ML.Location_ID, ML.On_Hand_Qty, ML.Unit_Cost

SELECT        ML.Material, ML.Location_ID, ML.On_Hand_Qty, ML.Unit_Cost
, SELECT MAX(Order_Date) FROM dbo.Job WHERE Part_Number = ML.Material)
FROM            dbo.Material_Location AS ML

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

SELECT        ML.Material, ML.Location_ID, ML.On_Hand_Qty, ML.Unit_Cost, ML.Last_Updated,
FROM            dbo.Material_Location ML LEFT OUTER JOIN (
                         SELECT Part_Number, MAX(Order_Date) AS Order_Date
                         FROM dbo.Job
                         GROUP BY Part_Number                        
                         ) AS J ON J.Part_Number = ML.Material

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial