Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - Join not working in HIVE


+
Ramasubramanian Narayanan... 2012-12-17, 12:30
+
Nitin Pawar 2012-12-17, 13:03
+
Ramasubramanian Narayanan... 2012-12-17, 13:20
+
Nitin Pawar 2012-12-17, 13:23
Copy link to this message
-
Re: Join not working in HIVE
Ramasubramanian Narayanan... 2012-12-17, 13:47
Hi,

We are trying to build a tree structure in a table.. hence we have the left
and right limits...
Can't use where clause in that..

regards,
Rams

On Mon, Dec 17, 2012 at 6:53 PM, Nitin Pawar <[EMAIL PROTECTED]>wrote:

> hive is not mysql  :)
>
>
> On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
> [EMAIL PROTECTED]> wrote:
>
>> Hi,
>>
>> But it is working fine in MySql...
>>
>> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>> A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and A1.rgt >>> A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
>> +-----+------+------+----------------------+------+---------+
>> | LVL | id   | code | short_name           | lft  | rgt     |
>> +-----+------+------+----------------------+------+---------+
>>
>> |   1 |    1 | 4    | Treasury Service     |    1 | 1000000 |
>> |   2 |    2 | 2    | Root                 |    2 |    1000 |
>> |   2 |    3 | Z    | CKC                  | 1001 |    2000 |
>> |   2 |    4 | A    | Treasury Service     | 2001 |    3000 |
>> |   3 |    5 | OOAQ | CODE CASH MANAGEMENT |    3 |     100 |
>> |   3 |    6 | YP00 | JPMC Treasury        |  101 |     200 |
>> |   3 |    7 | 432  | Treasury Service     | 1002 |    1100 |
>> +-----+------+------+----------------------+------+---------+
>>
>>
>> regards,
>> Rams
>>
>> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <[EMAIL PROTECTED]>wrote:
>>
>>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>>> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt)
>>> where A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft,
>>> A2.rgt
>>
>>
>>
>
>
> --
> Nitin Pawar
>
+
Nitin Pawar 2012-12-17, 13:55
+
Philip Tromans 2012-12-17, 14:18
+
Alexander Pivovarov 2012-12-17, 20:45