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 <nitinpawar...@gmail.com>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
