JOIN only latest row through a pivot table in one to many relation - MySQL [closed]

Question

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.

Answer 1

The query:

SELECT p.*, o.* 
FROM places AS p
LEFT JOIN (
    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