MySQL query - Internal join using only the latest version of a record

Question

I have a table called jobs with various pieces of information. Each task is assigned a task number (unique identifier).

Then there is another table called purchaseOrders that has the job identifier FK and PK of poID.

when the purchase order record is edited, the old information is saved ... value, I create a new PO record (new unique identifier).

What I'm trying to do is write one query that selects all fields from "jobs" and all fields from "purchaseOrders", but only the last poID for this job .

For instance:

 jobID Name State poID time jobID ========================== ========================== 1 foo fl 1 1:00 1 2 bar ga 2 1:10 1 3 zzz ny 3 1:20 1 4 2:00 2 5 2:01 2 6 2:30 2 7 3:00 3 8 3:40 3 9 3:15 3 

How can I run a query that will select all columns from both tables, but include only the information with the highest poID for a specific job identifier?

Answer 1

 SELECT a.*, c.* FROM jobs a INNER JOIN ( SELECT jobID, MAX(time) maxVal FROM purchaseOrders GROUP BY jobID ) b ON a.jobID = b.jobID INNER JOIN purchaseOrders c ON c.jobID = b.JobID AND c.time = b.maxVal 

• SQLFiddle Demo