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
I've also attached a table of sample data showing my anticipated output.
Any assistance would be very much appreciated.