Re: Outer-join a subselect?

In these situations I sometimes can get the problem solved with a UNION query. I take the subset where values match and union it to the subset where the values are missing.

select
a.col,
b.col
from
table1 a,
table2 b
where

    a.id = b.id
and a.sale_id in (select....)
UNION
select
a.col,
nvl(b.col, 0) col, -- usually use (NVL) null value logic if this is number
from
table1 a,
table2 b -- might need to create a view instead to simulate distinct result set you would get with subquery where
a.id = b.id(+)
and a.sale_id = b.sale_id(+)
and a.sale_id is null;

Sorry if that seems a bit cludgy but there are usually a number of ways to solve the problem, but I think if you look for a solution using UNION, VIEWS and EXISTS statement you will find it pretty quickly.

-Ethan

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 27 2000 - 00:00:00 CDT