Home » Developer & Programmer » Forms » joining 2 tables & selecting the most recent record via date field
joining 2 tables & selecting the most recent record via date field [message #86364] Fri, 24 September 2004 10:13 Go to next message
Mark
Messages: 284
Registered: July 1998
Senior Member
Hi,

I have 2 tables that I'm joining.  In 1 table, I have a list of all employees.  In the other, i have a list of cars associated with employees and non-employees (empnum in both tables).  In the list of cars, an employee may have more than one car (record).  I want to select the most recent record by date.  I have been able to extract what I want but it's hard coded.  I Oracle enough to be dangerous so any help would be appreciated.

Here is my statement:

select a.* from lease a , employee e where a.empno = e.empno and a.empno='081718'

and a.delivery_date = (select max(delivery_date) from lease where empno='081718')

This returns only the most recent record.  How can I make it generic for all records???

Thanks in advance!

Mark

Report message to a moderator

Re: joining 2 tables & selecting the most recent record via date field [message #86373 is a reply to message #86364] Sun, 26 September 2004 21:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
try the following

select a.* from lease a , employee e where a.empno = e.empno and a.delivery_date = (select max(delivery_date) from lease where empno=a.empno)

by
vamsi

Report message to a moderator

Re: joining 2 tables & selecting the most recent record via date field [message #86386 is a reply to message #86373] Mon, 27 September 2004 06:02 Go to previous messageGo to next message
Mark
Messages: 284
Registered: July 1998
Senior Member
Hi Vamsi,

I tried the SQL and made one change:
select a.* from lease a , employee e where a.empno = e.empno and a.delivery_date = (select max(delivery_date) from lease a, employee e where e.empno=a.empno)

This returns a single record of the maximum delivery_date of the lease table. I'm closer, but not
quite there yet. I'll keep trying and let you know what I come up with. Thanks for your help in the meantime!

Mark

Report message to a moderator

Re: joining 2 tables & selecting the most recent record via date field [message #86391 is a reply to message #86386] Mon, 27 September 2004 12:34 Go to previous message
Mark
Messages: 284
Registered: July 1998
Senior Member
OK - It took me all day...

select * from tfleet_lease_2_auto a , tfleet_employee e where a.empno = e.empno and delivery_date =(select max(delivery_date) from
tfleet_lease_2_auto where empno = a.empno) order by a.empno

this returns what I need...

Thanks for your help!

Report message to a moderator

Previous Topic: how can a choose directory path
Next Topic: Jinitiator Exception when we are trying to run forms 6i in Web
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 09 17:14:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.