How to select multiple MIN record of table that inner joinned with other 3 table based on other table parameter(s)?

Question

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.sstatic.net/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

id	...	prod_name	...	...	...	price
1	...	Toyota Agya	...	...	...	155500000
2	...	Toyota Calya	...	...	...	151600000

please help?

Answer 1

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