CODEWITHSUNDEEP
mysqlgroup-bysubquery
StuyvesantBlue
StuyvesantBlue

Reputation: 145

MySQL Two Table Min Value and Group By

I have two tables, products, and products_description

Products:

Product_ID    Product_Price
 1             10    
 2             20    
 3             10    
 4             20   
 5             10    
 6             20
 7             10    
 8             10   
 9             10    
10             10

Products_Description:

Product_ID    Product_Name
 1             Pants - Black    
 2             Pants - Black    
 3             Pants - White    
 4             Pants - White   
 5             Pants - Red    
 6             Pants - Red
 7             Hat    
 8             Socks   
 9             Scarf    
10             Bird

Basically I need to implement code with respect to the current code that's already present in the shopping cart.

The implementation needs to GROUP BY products_name - but provide the cheapest one.

So far I've got the following:

SELECT  p.products_id, p.products_price, pd.products_name

FROM products p

// bof custom sub-query

join
(
SELECT pp.products_id, pp.products_name, min(pr.products_price) as min_price
from products_description pp
inner JOIN products pr 
ON pp.products_id = pr.products_id
group by pp.products_name
)
AS subq on p.products_id = subq.products_id and p.products_price = subq.min_price

// eof custom sub-query

LEFT JOIN products_description pd on p.products_id = pd.products_id

Desired output:

Product_ID    Product_Name    Product_Price
 1             Pants - Black   10 
 3             Pants - White   10 
 5             Pants - Red     10
 7             Hat             10   
 8             Socks           10
 9             Scarf           10  
10             Bird            10

But what's happening is the pants is getting removed. And only products 7 - 10 are remaining behind....

Any ideas anyone please?

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You seem to want the cheapest product for each product name. The query is a bit complicated because the price and name information are separated. One way of writing the query is as:

select p.Product_ID, ppd.Product_Name, p.price
from product_description pd join
     products p
     on pd.product_id = p.product_id join
     (select pd2.Product_name, min(p2.price) as minprice
      from product_description pd2 join
           products p2
           on pd2.product_id = p2.product_id
      group by pd2.Product_name
     ) ppd
     on ppd.Product_Name = pd.Product_Name and
        ppd.minprice = p.price;

Upvotes: 1

Related Questions