how to outer join a sub query

[bookouri]

I have run into this same problem a couple of times. Since I cant outer join on a subquery, is there some way to work around it? For example something like:

select e.last_name, r.address, r.address_seq
from
employees e, residences r
where e.emp_id=r.emp_id
and r.address_seq = (select max(address_seq) from residences r2 where e.emp_id=r2.emp_id)

in this case we use address_seq to identify the most recent address on file. The problem occurs when the target has no residences on file. Normally I would outer join at "r.address_seq =" to get all records, but Oracle does not allow an outer join here.

Can anybody give me any suggestions how to work around this kind of problem?

thanks in advance

 

[kskid]

I would do a union

select e.last_name, r.address, r.address_seq
from
Do the following....

employees e, residences r
where e.emp_id=r.emp_id
and r.address_seq = (select max(address_seq) from residences r2 where e.emp_id=r2.emp_id)

union

select e.last_name, NULL, NULL
from
employees e
where not exists (select *
from residence r1
where r1.emp = e.emp)

-lw

 

[SantaMufasa]

...and you can do it with a single query (without a UNION):

select e.last_name, r.address, r.address_seq
  from employees e, residences r
 where e.emp_id=r.emp_id(+)
   and (r.address_seq is null
        or r.address_seq =
           (select max(address_seq)
              from residences r2
             where e.emp_id = r2.emp_id(+))
       )
/

Let us know how either of these techniques works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

The outer join will work also by making the subquery an inline view.

select e.last_name, r.address, r.address_seq
from
employees e
left outer join
( -- make inline view of max addresses
select
max(address_seq) as address_seq,
emp_id
from residences r2
group by emp_id
) vw
on vw.emp_id = e.emp_id
left outer join residences r
on e.emp_id=r.emp_id
and vw.address_seq = r.address_seq

 

[bookouri]

Thanks for the replies. I decided to use mufasa's example this time. But, Im going to work with both the other techniques. I never use unions and really need to understand them better. I have used inline code similar to cmmrfrds example before on something like this, but i had completely forgotten about it.

Thanks again for the suggestions.