CODEWITHSUNDEEP
mysqlsql
lucaswxp
lucaswxp

Reputation: 2119

Left joining only one result of corresponding table

I have the following tables:

products: id INT title VARCHAR

offers: id INT product_id INT price INT

A product can have many offers. I need to get all products with the corresponding offers with lower and higher prices, what I have is:

SELECT products.id, products.title, lowOffer.price, highOffer.price
FROM products

LEFT JOIN (SELECT offers.product_id, MIN(offers.price) AS price FROM offers GROUP BY offers.product_id) AS lowOffer ON (lowOffer.product_id = products.id)


LEFT JOIN (SELECT offers.product_id, MAX(offers.price) AS price FROM offers GROUP BY offers.product_id) AS highOffer ON (highOffer.product_id = products.id)

Now, I'm doing two subqueries, and I think I could have only one, because the two joins are working on the same set of offers. How can I do this?

My business logic is a little more complicated than this and the offers itself have relations and I'm gonna have to bring them too. How can I do this in a efficient way? I don't want to make the joins without needing to.

Upvotes: 1

Views: 120

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

You can left join, and then use GROUP BY on the top level:

SELECT
    p.id
,   p.title
,   MIN(o.price) as min_price
,   MAX(o.price) AS max_price
FROM products p
LEFT JOIN offers o ON o.product_id = p.id
GROUP BY p.id, p.title

Upvotes: 1

Related Questions