Rich and Alejandro, Have I got a query for you! :)
Using Alejandro's example, here is a query that would work: SELECT s.trans_id, b.price FROM sales s, rates a, rates b WHERE a.item=s.item AND b.item=a.item AND a.date < s.date GROUP BY s.trans_id, b.item, b.date HAVING b.date=MAX(s.date) Steve Meyers > At 07:52 PM 11/1/01, Alejandro Zuzenberg wrote: > >I need to select the appropriate price for a product in a table, and the > >product has a compund key with 2 fields: product number and date. > >For every transaction with a certain product, I need to retrieve the price > >that was 'current' at the specific time when that transaction took place. > > > >In joining two tables, I need to relate the price of a product to every > >sales transaction where that product was used, not only by product number > >but also by the effective date for that price. > > > >How can I do this? Is there a 'closest' function (the highest value that is > >< or = ) to be used in a WHERE statment ? > >I've been searching for this for hours and could not fnd a solution with > >MySql. > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
