Get Min Price From Product Variation Table in MySQL

Question

Product Table

Variation Table

How do I get a result from Product with every product's min price from Variation?

Answer 1

I join by description, which should work if no different products have the same description. It would be better to join by something more specific like product_id, but product_table does not have that as a column.

SELECT
     p.*,
     v.min(price) as min_price,
FROM
     variation_table v
JOIN
     product_table p ON v.description = p.description;

You could probably add ORDER BY product_id to do in order of the product ids or whatever other field you'd prefer.