Re: Outer Join on a Correlated Sub Query

From: <puneetbans_at_gmail.com>
Date: 28 Sep 2006 09:30:07 -0700
Message-ID: <1159461007.675919.49720@d34g2000cwd.googlegroups.com>

Here's my simplifed query

select

           b.id
from

	   tab1 a,
	   tab2 b,
           tab3 c
where
          a.dig_asset_i = c.parnt_dig_asset_i(+)
          and
         (select a1.dig_asset_i  from  tab1 a1  where a1.dig_asset_i =
c.chld_dig_asset_i) =                b.dig_asset_i

I want the join with the sub query to b.dig_asset_i to be an outer join but Oracle doesn't allow it. With the above query, I don't get any rows for dig_asset_i in tab1 for which there is no dig_asset_i in tab2. Using ANSI syntax doesn't help either as then I have to move the join condition to the 'From' clause and correlated sub query doesnt' work there.

The table structures are

tab 1 - dig_asset_i
tab 2 - dig_asset_i, id
tab 3 - parnt_dig_asset_i, chld_dig_asset_i (these can be compared to

dig_asset_i in tab1. Basically I am trying to find the data in tab2 for dig_asset_i which are children of dig_asset_i mentioned in tab1. tab1 is the master table for dig_asset_i and contains both parent and children dig_asset_i. tab3 is the table containing the parent child relationship between these tables.

Puneet

Ashish wrote:
> Have you tried using the new ANSI SQL syntax introducted in 9i. It is
> something like SELECT .... FROM table_a OUTER JOIN table_b ON ...
> (Sorry, please check the exact syntax).
>
>
> puneetbans_at_gmail.com wrote:
> > I am using Oracle 9i. I have a join condition where the left side of
> > the join is a correlated sub query and I want to outer join it to
> > another table. However, Oracle does not allow the use of (+) operator
> > with sub queries. A common solution suggested to this problem is to use
> > in-line views. This solution works only if the sub query is not a
> > correlated sub query as the in-line query gets executed first. Does
> > anybody have any idea how to make the outer join work with a correlated
> > sub query?
Received on Thu Sep 28 2006 - 11:30:07 CDT