I have two tables
1.Product table having columns
- prod_code
- alert_limit
- prod_description
2.Daily_data table having columns
- prod_code
- used_today
- date
- stock_left
How should i join these two tables to get recent records of each product on the basis of date ? I have tried
SELECT p.*, d.*
FROM product p
JOIN daily_data d ON (p.product_code = d.prod_code)
LEFT OUTER JOIN daily_data d2 ON (p.product_code = d.prod_code AND
(d.date < d2.date OR d.date = d2.date AND d.prod_code < d2.prod_code))
WHERE d2.prod_code IS NULL;
but the above is giving me one recent row of the last product added