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 <sw...@netflix.com> wrote: > I think you can also move the condition T2.field6='yyyyyyy' into the ON > clause. > > > > > > From: Igor Tatarinov [mailto:i...@decide.com] > Sent: Friday, June 10, 2011 9:31 PM > To: user@hive.apache.org > 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 <sn...@attributor.com> 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 > >
