MIN() on subquery results

Question

I'm pretty new to SQL and this might be a really dumb and easy question, but I couldn't solve it myself, so here's the scenario: I have two tables, products and productvariations kinda like this.

products

idproducts | product | ... | checked
-------------------------------------
1          | testpr1 | ... | 0
2          | testpr2 | ... | 0
3          | testpr3 | ... | 1
...

productvariations

productid | colorvalue | price_single | price_palette | ...
------------------------------------------------------------
1         | 0          | 12.50        | 11.50         | ...
1         | 1          | 10.25        | 10.00         | ...
2         | 0          | 14.50        | 13.75         | ...
2         | 1          | 13.00        | 11.50         | ...
2         | 1          | 12.50        | 12.00         | ...
...

With 2x LEFT JOIN I try to get the respective price_single and price_palette as well as some other columns from the productvariations table depending on the colorvalue. The first LEFT JOIN with colorvalue = '0' works perfect, since there's only one row per product with colorvalue = '0'. But the second LEFT JOIN returns a new resultrow for every row that has colorvalue = '1' of the respective product, since there can be several rows with that colorvalue. This is the code I have so far:

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette, productvar1.price_specialconditions, productvar1.price_colorchange,
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.product AND productvar1.colorvalue = '0' 
LEFT JOIN productvariations AS productvar2 
ON products.idproducts = productvar2.product AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

From questions like this (LEFT JOIN only first row) I understand that I somehow have to use MIN() in a subquery. But since I'm pretty new to SQL I don't really understand how to exactly apply this. This is what I tried and I know it doesn't make sense at all, but this was one of my desperate tries yesterday to understand and make it work:

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette, productvar1.price_specialconditions, productvar1.price_colorchange, 
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.product AND productvar1.colorvalue = '0' 
LEFT JOIN 
    (SELECT product, MIN(price_single), MIN(price_palette) 
    FROM productvariations AS productvar2
    WHERE productvar2.products = producst.idproducts AND productvar2.colorvalue = '1'
    GROUP BY product) AS productvar2
ON products.idproducts = productvar2.product AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

WORKING SOLUTION by shubham

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette 
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.productid AND productvar1.colorvalue = '0' 
LEFT JOIN 
    (SELECT productvar.productid, MIN(productvar.price_single)as price_single , MIN(productvar.price_palette) as price_palette,productvar.colorvalue 
    FROM productvariations AS productvar inner join products on productvar.productid = products.idproducts
    WHERE productvar.productid = products.idproducts AND productvar.colorvalue = '1'
    GROUP BY productvar.productid) AS productvar2
ON products.idproducts = productvar2.productid AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

Hi @iMorces, please share your expected output based on your sample data. — Rahul Biswas, Jul 23 '21 at 09:18

score 1 · Accepted Answer · answered Jul 23 '21 at 09:29

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette 
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.productid AND productvar1.colorvalue = '0' 
LEFT JOIN 
    (SELECT productvar.productid, MIN(productvar.price_single)as price_single , MIN(productvar.price_palette) as price_palette,productvar.colorvalue 
    FROM productvariations AS productvar inner join products on productvar.productid = products.idproducts
    WHERE productvar.productid = products.idproducts AND productvar.colorvalue = '1'
    GROUP BY productvar.productid) AS productvar2
ON products.idproducts = productvar2.productid AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

Here is SQL Fiddle link, I am not sure what you want but I think this maybe help.

That's it, this one does the trick. Now I'll try and understand what's happening. Thanks for your help. — iMorces, Jul 23 '21 at 09:46

MIN() on subquery results

1 Answers1