Hello, I hope someone can help me with my problem. I want to join 2 tables. One contains data about start and finish dates of a machine install, the second table conatins data of install costs and a commencing date. Table 1: +---------+------------+------------+---------+---------+ | Machine | StartDate | StopDate | Country | ObjType | +---------+------------+------------+---------+---------+ | 1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | | 1111 | 2005-10-03 | 2006-01-02 | JP | AA002 | +---------+------------+------------+---------+---------+ Table 2: +---------+------------+------+ | ObjType | ComDate | Rate | +---------+------------+------+ | AA001 | 2004-01-01 | 150 | | AA001 | 2005-01-01 | 90 | | AA002 | 2005-01-01 | 100 | | AA002 | 2005-03-15 | 95 | | AA002 | 2005-11-01 | 110 | +---------+------------+------+
Result: SELECT m.*, r.ComDate, r.Rate FROM machinedata m LEFT JOIN Rates r ON m.ObjType=r.ObjType; +---------+------------+------------+---------+---------+------------+-- ----+ | Machine | StartDate | StopDate | Country | ObjType | ComDate | Rate | +---------+------------+------------+---------+---------+------------+-- ----+ | 1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2004-01-01 | 150 | | 1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2005-01-01 | 90 | | 1111 | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-01-01 | 100 | | 1111 | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-03-15 | 95 | | 1111 | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-11-01 | 110 | +---------+------------+------------+---------+---------+------------+-- ----+ As you can see, I get 4 rows back from the query. And both machines are mentioned twice. But what I want is that I get just 2 rows with the latest commencing date (ComDate) based on when the machine has started with the rate that belongs to the commencing date. So the result that I want is Like this: +---------+------------+------------+---------+---------+------------+-- ----+ | Machine | StartDate | StopDate | Country | ObjType | ComDate | Rate | +---------+------------+------------+---------+---------+------------+-- ----+ | 1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2005-01-01 | 90 | | 1111 | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-03-15 | 95 | +---------+------------+------------+---------+---------+------------+-- ----+ How can I create a query to get this result? Can somebody help me? TIA, Arjan Hulshoff. -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
