1

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

..... .....

..... .....

..... .....

1
  • whar happens when Max po.date gas two ids or even more?
    – nbk
    Aug 11 '20 at 22:25
1

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.