MySQL query - Inner join using only the latest version of an entry

Question

I have a table, named jobs with various pieces of information. Each job is given a job number (unique id).

There is then another table, named purchaseOrders that has a FK of jobID and a PK of poID.

when a purchase order entry is edited, the old information is saved... meaning, i create a new PO entry (new unique id).

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

For example:

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 the columns from both tables, but only include the information with the highest poID for the specific jobID?

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