hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sagar naik <sn...@attributor.com>
Subject Re: left outer join on same table
Date Sun, 12 Jun 2011 22:48:16 GMT
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 <swong@netflix.com> wrote:
> I think you can also move the condition T2.field6='yyyyyyy' into the ON
> clause.
>
>
>
>
>
> From: Igor Tatarinov [mailto:igor@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 <snaik@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
>
>

Mime
View raw message