Hello, I need to write a SQL script that returns an employee with their last three pay increases. There are two tables employees and pay_changes For each employee in the employees table I need to return a row as so: Employee Name | Pay Amt 1 | Pay Amt 2 | Pay Amt 3 I can accompilish this by using three references to the pay_changes table: select e.name, p1.chg_amt, p2.chg_amt, p3.chg_amt from employees e , pay_changes p1 , pay_changes p2 , pay_changes p3 where e.person_id = p1.person_id and sysdate >= p1.change_date and e.person_id = p2.person_id and p2.change_date = p1.last_change_date and e.person_id = p3.person_id and p3.change_date = p2.last_change_date The problem is that employees with 2 or less pay changes are not getting picked up in the join. I have tried using a query with multiple OUTER JOINS but I cannot get that to work correctly either. Any suggestions? Thanks for any help in advance. M

Think I've sorted it. You didn't mention this problem but when I used your code on my sample, I was getting any employees who had 4 changes appearing twice, (as technically they've got two sets of three changes). I solved the problem by using LEFT JOINs (which basically gives you every record), then using an INNER JOIN to a subquery which identifies each maximum change date (below the specified system date), so you only get the most recent 3 changes. Please note I'm using MySQL which is very forgiving when it comes to syntax for specifying joins...if memory serves me correctly, on MS SQL 2000 you need to put brackets round the FROM clause, closing each just before the next JOIN. Note hopefully this will kick out in an ok format, I've copied directly from my command prompt shell. select * from pay_changes ----------+ | change_id | person_id | change_date | last_change_date | chg_amt | ----------+ | 1 | 1 | 2008-12-01 | 2008-11-01 | 100.00 | | 2 | 1 | 2008-11-01 | 2008-10-15 | 500.00 | | 3 | 1 | 2008-10-15 | 0000-00-00 | 1000.00 | | 4 | 2 | 2008-12-01 | 2008-11-01 | 5000.00 | | 5 | 2 | 2008-11-01 | 0000-00-00 | 5000.00 | | 6 | 3 | 2008-12-01 | 0000-00-00 | 25000.00 | | 7 | 4 | 2008-11-01 | 2008-10-01 | 50000.00 | | 8 | 4 | 2008-10-01 | 2008-09-25 | 1000.00 | | 9 | 4 | 2008-09-25 | 2007-01-01 | 2000.00 | | 10 | 4 | 2007-01-01 | 0000-00-00 | 12000.00 | ----------+ select * from employees --------+ | person_id | name | pay_amt | --------+ | 1 | Joe Bloggs | 10000.00 | | 2 | G Person | 20000.00 | | 3 | H Hall | 50000.00 | | 4 | D Smith | 100000.00 | --------+ mysql> select e.name, p1.chg_amt, p2.chg_amt, p3.chg_amt from employees AS e LEF T JOIN pay_changes AS p1 ON e.person_id=p1.person_id AND 20081218>=p1.change_dat e LEFT JOIN pay_changes AS p2 ON p1.person_id=p2.person_id AND p2.change_date=p1 .last_change_date LEFT JOIN pay_changes AS p3 ON p2.person_id=p3.person_id AND p 3.change_date=p2.last_change_date INNER JOIN (SELECT p4.person_id AS pid, max(ch ange_date) AS maxdate from pay_changes AS p4 WHERE p4.change_date<=20081218 GROU P BY p4.person_id) AS sq1 ON p1.person_id=sq1.pid AND sq1.maxdate=p1.change_date ; -----+---------+ | name | chg_amt | chg_amt | chg_amt | -----+---------+ | Joe Bloggs | 100.00 | 500.00 | 1000.00 | | G Person | 5000.00 | 5000.00 | NULL | | H Hall | 25000.00 | NULL | NULL | | D Smith | 50000.00 | 1000.00 | 2000.00 | -----+---------+