Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> left outer join on same table


Copy link to this message
-
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
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB