Re: Outer Join on a Correlated Sub Query

From: <puneetbans_at_gmail.com>
Date: 4 Oct 2006 10:07:14 -0700
Message-ID: <1159981634.899411.129650@m73g2000cwd.googlegroups.com>

Bang on target, Charles! Thanks very much. Your solution works perfectly. Actually, I do need the second reference to tab1 and then it works perfectly. I am mad at myself that I didn't think of putting in a second reference to tab1. Unfortunately, my app goes to production in a week and I can't modify the query now but the query you have suggested is definitely better. I found an alternative solution to the correlated subquery outer join problem. Instead of trying to join in the same query, I split them into two queries and joined them one level above. But your solution is definitely more elegant.

Puneet

Charles Hooper wrote:
> puneetbans_at_gmail.com wrote:
> > 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).
> > 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
> >
> Try simplify the SQL statement again. This SQL statement should be
> equivalent to the one that you posted:
> SELECT
> B.ID
> FROM
> TAB1 A,
> TAB1 A1,
> TAB2 B,
> TAB3 C
> WHERE
> A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
> AND C.CHLD_DIG_ASSET_I=A1.DIG_ASSET_I
> AND A1.DIG_ASSET_I=B.DIG_ASSET_I;

>

> It appears that the last two restrictions in the WHERE clause make it
> unnecessary to reference TAB1 a second time. The SQL statement can
> then be refined again:
> SELECT
> B.ID
> FROM
> TAB1 A,
> TAB2 B,
> TAB3 C
> WHERE
> A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
> AND C.CHLD_DIG_ASSET_I=B.DIG_ASSET_I;

>

> The above should give you problems if an outer join is created between
> table A and table C, so you will likely need to format it like this:
> SELECT
> B.ID
> FROM
> TAB1 A,
> TAB2 B,
> TAB3 C
> WHERE
> A.DIG_ASSET_I=C.PARNT_DIG_ASSET_I(+)
> AND C.CHLD_DIG_ASSET_I=B.DIG_ASSET_I(+);

>

> Maybe I am misunderstanding the description of your problem?

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Wed Oct 04 2006 - 12:07:14 CDT