Hi ,

I am trying to do join on same table.
I wanted to do a left outer join
However, the results are not like left outer join( with no null values)

select T1.field1, T1.field2, T1.field3
          T2.field10
          from table T1
          left outer join table T2 on (T1.key=T2.key and
T1.partition='p1' and T2.partition='p2')
           where
           T1.field5='xxxxx' and T2.field6='yyyyyyy;'
I tried left/full  all join result are like  inner join.
What am I missing here ?
 is it some mistake on my part or some optimization /flag to be turned off

Thanks for the help

-Sagar

The condition T2.field6='yyyyyyy;' is tested after the outer join. As a
result you won't see any non-matching results.
You'll need a subquery to enforce that condition. Alternatively, adding ' OR
T2.field6 IS NULL' might work for you too. Just make sure to use parens
around the OR clause. But that's probably not what you want - then, use a
subquery.
On Fri, Jun 10, 2011 at 6:55 PM, sagar naik <[EMAIL PROTECTED]> wrote:

> Hi ,
>
> I am trying to do join on same table.
> I wanted to do a left outer join
> However, the results are not like left outer join( with no null values)
>
> select T1.field1, T1.field2, T1.field3
>          T2.field10
>          from table T1
>          left outer join table T2 on (T1.key=T2.key and
> T1.partition='p1' and T2.partition='p2')
>           where
>           T1.field5='xxxxx' and T2.field6='yyyyyyy;'
>
>
> I tried left/full  all join result are like  inner join.
> What am I missing here ?
>  is it some mistake on my part or some optimization /flag to be turned off
>
> Thanks for the help
>
>
>
> -Sagar
>

I think you can also move the condition T2.field6='yyyyyyy' into the ON clause.
From: Igor Tatarinov [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 10, 2011 9:31 PM
To: [EMAIL PROTECTED]
Subject: Re: left outer join on same table

The condition T2.field6='yyyyyyy;' is tested after the outer join. As a result you won't see any non-matching results.
You'll need a subquery to enforce that condition. Alternatively, adding ' OR T2.field6 IS NULL' might work for you too. Just make sure to use parens around the OR clause. But that's probably not what you want - then, use a subquery.

On Fri, Jun 10, 2011 at 6:55 PM, sagar naik <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
Hi ,

I am trying to do join on same table.
I wanted to do a left outer join
However, the results are not like left outer join( with no null values)

select T1.field1, T1.field2, T1.field3
         T2.field10
         from table T1
         left outer join table T2 on (T1.key=T2.key and
T1.partition='p1' and T2.partition='p2')
          where
          T1.field5='xxxxx' and T2.field6='yyyyyyy;'
I tried left/full  all join result are like  inner join.
What am I missing here ?
 is it some mistake on my part or some optimization /flag to be turned off

Thanks for the help

-Sagar

Thxs Igor

Trick is to get where conditions in join condition

Thxs again

-Sagar

On Sat, Jun 11, 2011 at 5:01 PM, Steven Wong <[EMAIL PROTECTED]> wrote:
> I think you can also move the condition T2.field6='yyyyyyy' into the ON
> clause.
>
>
>
>
>
> From: Igor Tatarinov [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 10, 2011 9:31 PM
> To: [EMAIL PROTECTED]
> Subject: Re: left outer join on same table
>
>
>
> The condition T2.field6='yyyyyyy;' is tested after the outer join. As a
> result you won't see any non-matching results.
>
> You'll need a subquery to enforce that condition. Alternatively, adding ' OR
> T2.field6 IS NULL' might work for you too. Just make sure to use parens
> around the OR clause. But that's probably not what you want - then, use a
> subquery.
>
>
>
> On Fri, Jun 10, 2011 at 6:55 PM, sagar naik <[EMAIL PROTECTED]> wrote:
>
> Hi ,
>
> I am trying to do join on same table.
> I wanted to do a left outer join
> However, the results are not like left outer join( with no null values)
>
> select T1.field1, T1.field2, T1.field3
>          T2.field10
>          from table T1
>          left outer join table T2 on (T1.key=T2.key and
> T1.partition='p1' and T2.partition='p2')
>           where
>           T1.field5='xxxxx' and T2.field6='yyyyyyy;'
>
>
> I tried left/full  all join result are like  inner join.
> What am I missing here ?
>  is it some mistake on my part or some optimization /flag to be turned off
>
> Thanks for the help
>
>
>
> -Sagar
>
>