Mysql – How to i use the same query for all codes

MySQL

I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) :

I want to return the last price for the last date of product code

   SELECT invent.code_product,products.supp,products.price,products.date  
   FROM products

   LEFT JOIN list_product ON products.supp = list_product.supp
   LEFT JOIN invent on list_product.id_product = invent.fk_product

   WHERE invent.code_product= "XXX185"
   ORDER BY products.date  DESC
   LIMIT 1 ;

This is the result for one code :

The query works for one code and limit it the result in 1.

How can i use the same query to return the last date and price for all codes?

Best Answer

It sounds like you want something like this:

SELECT invent.code_product,products.supp,products.price, 
       MAX(products.date) AS Date
FROM products 
LEFT JOIN list_product ON products.supp = list_product.supp
LEFT JOIN invent on list_product.id_product = invent.fk_product
Group by  invent.code_product,products.supp,products.price
Order by  products.date

To fully understand your comments on Date and Hour, it would be good to see your data definition and some representative data.

Related Solutions

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ? In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select 
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE 
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.

Mysql – How to i return the date of max id

You don't need a subquery for this. It can be simple and more efficient:

SELECT id_product, date  
FROM products
WHERE fk_stat = 1
  AND id_order = 77185 
ORDER BY id_product DESC
LIMIT 1 ;

Best Answer

Related Solutions

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

Mysql – How to i return the date of max id

Related Question