Get max value when joining 3 tables in SQL This is based on a MySQL database.
I'm trying to join 3 tables together and get a MAX value from the third one. I'm trying to get the po.id, for each part.id, that has the latest po.date. I cannot base it on the MAX po.id. It must be based on the MAX po.date, which is what I'm stuck on. However, I need to used the poitem table to determine if the po contains the part.
Table: po
po.id, po.date
Table: poitem
poitem.id (item id that is only used for joining), poitem.partid
Table: part
part.id
This is what I'm trying to end up with:
part.id                po.id
.....                  .....
.....                  .....
.....                  .....
Assuming that  poitem.id is the same as po.id
SELECT
     pa.id,
     pi.id
FROM
    part pa INNER JOIN poitem pi ON pi.partid = pa.id
WHERE pi.id IN (SELECT po.id 
                FROM po 
                WHERE po.`date` = (SELECT MAX(`date`) FROM po))