0

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@domain.com
2 Place B 000000 email@placeb.com
3 Place C 000000 email@placec.com

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.

Sk1ppeR
  • 137
  • 1
  • 5

1 Answers1

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@placeb.com 236 Offer C for Place B 00000000 $950
3 Place C 000000 email@placec.com 237 Offer A for Place C 00000000 $500
1 Place A 000000 email@domain.com null null null null
Silvanu
  • 124
  • 2