-2

Product Table This is Product Table

Variation Table This is variation table

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

philipxy
  • 14,867
  • 6
  • 39
  • 83
Vishal Sarvaiya
  • 19
  • 4
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [ask] [Help] [mre] [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Aug 04 '22 at 06:28

1 Answers1

0

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
DAking
  • 7
  • 3
  • Thanks For anser What I execute SELECT `products`.*,`category`.`name` as `category_name`, `sub_category`.`name` as `sub_category_name`, `brand`.`name` as `brand_name` FROM `products` LEFT JOIN `category` ON `products`.`category_id` = `category`.`id` LEFT JOIN `sub_category` ON `products`.`sub_category_id` = `sub_category`.`id` LEFT JOIN `brand` ON `products`.`brand_id` = `brand`.`id` LEFT JOIN ( SELECT `product_id`, MIN(CAST(`price` AS UNSIGNED)) as min_price FROM `product_variation` GROUP BY `product_id` ) as pv ON `products`.`id` = `pv`.`product_id` – Vishal Sarvaiya Aug 04 '22 at 07:05