Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: domain of snaik@attributor.com designates
 209.85.218.48 as permitted sender)
MIME-Version: 1.0
In-Reply-To: <4F6B25AFFFCAFE44B6259A412D5F9B1032B8A5FC@ExchMBX104.netflix.com>
References: <BANLkTiknvFnivYY=vepTAUPkFtm5pgUK6Q@mail.gmail.com>
 <BANLkTinoM-3Q58eqaZ0jahvAH2xf4L4JXQ@mail.gmail.com>
 <4F6B25AFFFCAFE44B6259A412D5F9B1032B8A5FC@ExchMBX104.netflix.com>
From: sagar naik <snaik@attributor.com>
Date: Sun, 12 Jun 2011 15:48:16 -0700
Message-ID: <BANLkTikzN-oMM690Q1iXfFHXj2XtLFydbA@mail.gmail.com>
Subject: Re: left outer join on same table
To: user@hive.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=3D'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=A0T2.field6=3D'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
> =A0 =A0 =A0 =A0 =A0T2.field10
> =A0 =A0 =A0 =A0 =A0from table T1
> =A0 =A0 =A0 =A0 =A0left outer join table T2 on (T1.key=3DT2.key and
> T1.partition=3D'p1' and T2.partition=3D'p2')
> =A0 =A0 =A0 =A0 =A0 where
> =A0 =A0 =A0 =A0 =A0 T1.field5=3D'xxxxx' and T2.field6=3D'yyyyyyy;'
>
>
> I tried left/full =A0all join result are like =A0inner join.
> What am I missing here ?
> =A0is it some mistake on my part or some optimization /flag to be turned =
off
>
> Thanks for the help
>
>
>
> -Sagar
>
>