[SOLVED] MySQL select min price from 2 connected tables

MySQL select min price from 2 connected tables

I'm working on custom e-commerce solution but I don't know how can I select MIN price from 2 columns on 2 different tables.

TABLE: shop_products
id
title
price
price_sale

TABLE: shop_products_variants
id
product_id
price
price_sale

So table shop_products hasMany shop_products_variants. You can specify price or price_sale on shop_products table but you can also specify price or price_sale for custom variant. I need to select products with minimal price for each product (including product variants). I was able to find MIN price in variants but I'm not able to combine MIN price from shop_products and MIN price from shop_products_variants.

CASE WHEN price_sale IS NOT NULL THEN price_sale ELSE price END AS price_min

I would appreciate any help...

Solution

You can join 'shop_product' table and 'shop_products_variants' table, after it you can find minimal price in result set. For example:

SELECT sp.id,
       sp.title,
       MIN(LEAST( IFNULL(sp.price_sale, sp.price, sp.price_sale), 
                  IFNULL(spv.price_sale, spv.price, spv.price_sale) 
       )) AS min_price
FROM shop_products AS sp
LEFT JOIN shop_products_variants AS spv ON shp.product_id = sp.id
GROUP BY sp.id,
         sp.title