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 >
