I have 3 tables and I only need to compare data between 2 of them but only taking in mind the latest row. The structure of the table is as follows

Table Places

id brand phone email
1 Place A 000000 [email protected]
2 Place B 000000 [email protected]
3 Place C 000000 [email protected]

Table Offers

id name vendor_phone price
234 Offer A for Place B 00000000 $1000
235 Offer B for Place B 00000000 $900
236 Offer C for Place B 00000000 $950
237 Offer A for Place C 00000000 $500

Pivot table

id place_id offer_id created_at
1 2 234 NOW()
2 2 235 NOW()
3 2 236 NOW()
4 3 237 NOW()

Basically I wish to craft a query where I could take all the places data (places.*) joined with the latest offer (offer.*) data in 1 row.

When I join regularly I get a lot of duplicate rows. If I use GROUP BY statement I am not guaranteed to join the latest offer data from the offers table. Pivot tables confuse me.


1 Answer 1


The query:

SELECT p.*, o.* 
FROM places AS p
    SELECT place_id, MAX(offer_id) AS latest_offer_id
    FROM place_offer
    GROUP BY place_id
) AS po ON po.place_id = p.id
LEFT JOIN offers AS o ON o.id = po.latest_offer_id

will output:

id brand phone email id name vendor_phone price
2 Place B 000000 [email protected] 236 Offer C for Place B 00000000 $950
3 Place C 000000 [email protected] 237 Offer A for Place C 00000000 $500
1 Place A 000000 [email protected] null null null null

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