Extract Reords via SQL

[jayjaybigs]

I was wondering if anyone has an sql idea that could help with the following:

I have this file with the folloing record layout:

empid , Name, effdate, home_date, salary_date
200095,BIB STEVEN, 5/1/2003 10/1/1993 5/1/2003
200095,BIB STEVEN, 5/1/2004 10/1/1993 5/1/2003
200099,BEN TAMMY, 5/29/2000 5/29/2000 5/04/2002
200099,BEN TAMMY, 5/23/2001 5/23/2001 1/1/2001


I am trying to extract the record for each empid where salary_date does not exist in either effdate and home_date for a particular employee(emp_id)

Hence my result would be like following:
200095,BIB STEVEN, 5/1/2004 10/1/1993 5/1/2003
200099,BEN TAMMY, 5/29/2000 5/29/2000 5/04/2002


Here is my effort:

select *
from mytable a
where a.salary_date not in (select b.effdate, b.home_date
from mytable b
where b.empid = a.empid)

However this gives me error as there two sets of column in my subquery and on column before my no in cluase, please help

 

[mrdenny]

Your getting to complex.

select *
from mytable
where a.salary_date not between a.home_date and a.effdate

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[PHV]

Either:
select *
from mytable a
where a.salary_date not in (a.effdate, a.home_date)

Or:
select *
from mytable a
where a.salary_date <> a.effdate
and a.salary_date <> a.home_date

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

If the test is against all the records of the employee:
select *
from mytable a
where a.salary_date not in (select b.effdate
from mytable b
where b.empid = a.empid)
and a.salary_date not in (select c.home_date
from mytable c
where c.empid = a.empid)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carp]

You have already received your answer in the Oracle forum.