I have a question. I have 4 tables:

1. product_list
2. product
3. product_img
4. pricelist

This what my query looks like:

SELECT
  product_list.id,
  product_list.class,
  product.prod_name,
  product.prod_url,
  product.prod_overview,
  product_img.list_prod340x340,
  pricelist.price 
FROM 
(
  (
    (
      product_list 
      INNER JOIN product ON product_list.id = product.prod_list_id
    ) 
    INNER JOIN product_img ON product.id = product_img.prod_id
  ) 
  INNER JOIN pricelist ON product.id = pricelist.prod_id
)
ORDER BY product_list.id, pricelist.price ASC

This is the result of the query [QUERY RESULT] [1]: https://i.stack.imgur.com/V9JO1.jpg

So the question is, how can i get only the lowest price of each prod_name.

This is how it should be returned

please help?

CodePudding user response：

Please test this:

SELECT product_list.id, product_list.class, 
product.prod_name, product.prod_url, 
product.prod_overview, product_img.list_prod340x340, MIN(pricelist.price)
FROM (((product_list 
INNER JOIN product 
    ON product_list.id = product.prod_list_id) 
INNER JOIN product_img 
    ON product.id = product_img.prod_id) 
INNER JOIN pricelist 
    ON product.id = pricelist.prod_id)
GROUP BY product_list.id, product_list.class, product.prod_name, product.prod_url,product.prod_overview, product_img.list_prod340x340
ORDER BY product_list.id, pricelist.price ASC

CodePudding user response：

The easiest approach is probably to get the price in a subquery:

SELECT
  pl.id,
  pl.class,
  p.prod_name,
  p.prod_url,
  p.prod_overview,
  pi.list_prod340x340,
  (
    SELECT MIN(price)
    FROM pricelist prl
    WHERE prl.price = p.id
  ) AS min_price
FROM product_list pl
INNER JOIN product p ON p.prod_list_id = pl.id
INNER JOIN product_img pi ON pi.prod_id = p.id
ORDER BY pl.id, p.id;

Since MySQL 8.0.14 you can move the subquery to the FROM clause and make this a lateral join, which allows you to select more than one column from the price table:

SELECT
  pl.id,
  pl.class,
  p.prod_name,
  p.prod_url,
  p.prod_overview,
  pi.list_prod340x340,
  prli.price
FROM product_list pl
INNER JOIN product p ON p.prod_list_id = pl.id
INNER JOIN product_img pi ON pi.prod_id = p.id
CROSS JOIN LATERAL
(
  SELECT *
  FROM pricelist prl
  WHERE prl.price = p.id
  ORDER BY prl.price
  LIMIT 1
) prli
ORDER BY pl.id, p.id;