Querying records via a outer join

[meridian99]

Hi fellow SQlers...

I'm trying to do a sql command that will return the records that are NOT in another associated oracle table.

select * from employees
where employeeid not found in employee_history

Need to retrieve contractors which are in the employees file but do not exist in the employee_history file.

thanks for your help ...

 

[LKBrwnDBA]

Maybe you could try:

select * from employees 
where employeeid not in (
select employeeid from employee_history)

--or--

select * from employees e
where employeeid not exists (
select 1 from employee_history h
where h.employeeid = e.employeeid)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[joebednarz]

In Oracle you have another option:

SELECT e.* FROM employees e, employee_history h
WHERE e.employeeid = h.employeeid(+)
  AND h.employeeid IS NULL;

JoeB
Use smaller fonts to save disk space...

 

[LKBrwnDBA]

joe, he want to return the records that are NOT in another associated oracle table.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[joebednarz]

Yes, the query I posted will do just that. Outer joins using the (+) notation denotes the dependant/child table, and the "h.employeeid IS NULL" says only show records in employee where there is no match in history.

I was going off of the sql-pseudo code orginally posted:

select * from employees
where employeeid not found in employee_history

It's similar to your first query, just doing a join instead of a sub-query...

JoeB
Use smaller fonts to save disk space...

 

[Turkbear]

Hi,
As always, there is yet another way:

Select * from employee where employee_id IN
(
select employee_id from employee
MINUS
select employee_id from employee_history
);

Will only return records whose employee_id is NOT in employee_history.









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

True" to all of the above, but I like the simple elegance of JoeBednarz's suggestion.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.