Niels Perfors Niels Perfors - 6 months ago 28
MySQL Question

Left Join on MAX(DATE)

I have 2 tables: Transactions (a) and Prices (b). I want to retrieve the price from table b that is valid on the transaction date.

Table a contains a history of article transactions:
Store_type, Date, Article, ...

Table b contains a history of article prices:
Store_type, Date, Article, price

Currently i have this:

Select
a.Store_type,
a.Date
a.Article,
(select b.price
from PRICES b
where b.Store_type = a.Store_type
and b.Article = a.Article
and b.Date = (select max(c.date)
from PRICES c
where c.Store_type = a.Store_type
and c.Article = a.Article
and c.date <= a.date)) AS ART_PRICE
from TRANSACTIONS a


It works just fine but it seems to take very long because of the double subquery.
Can the same be done with a LEFT JOIN?

rcs rcs
Answer

Can try using the below query ?

SELECT      a.Store_type, a.Date, a.Article, b.Price
FROM        TRANSACTIONS a
LEFT JOIN   PRICES b ON a.Store_type = b.Store_type
AND         a.Article = b.Article
AND         b.Date = (SELECT   MAX (c.Date) 
                      FROM     PRICES c 
                      WHERE    a.Store_type = c.Store_Type
                      AND      a.Article = c.Article
                      AND      c.Date <= a.Date)

It still has one subquery though, used to retrieve the maximum date.