How to get all children of a node in tree structure ? SQL query?

Question

table - user

columns - (userId ,name, managerId)

rows -

(1,nilesh,0)
(2,nikhil,1)    
(3,nitin ,2)  
(4,Ruchi,2)

if I give id of user it should list all reporting people to him . if I give userId = 2 it should return 3,4.

Is this query correct

SELECT ad3.userId
FROM user au , user  au2 , user  au3
WHERE 
    ad.managerId = ad2.managerId AND 
    ad3.managerId = ad2.userId AND
    ad.userId=2

Is there any efficent way to manage tree structure in DB ? How about right and left leaf way ?

Answer 1

I use a text field to deal with trees in SQL. It's easier than using left/right values.

Lets take the example from the MySQL article:

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

It would result in a table like this:

Id      ParentId        Lineage     Name

1       null            /1/         ELECTRONICS
2       1               /1/2/       TELEVISIONS
3       2               /1/2/3/     TUBE
4       2               /1/2/4/     LCD
5       2               /1/2/5/     PLASMA
6       6               /1/6/       GAME CONSOLES
7       1               /1/7/       PORTABLE ELECTRONICS
8       7               /1/7/8/     MP3 PLAYERS
9       8               /1/7/8/9/   FLASH
10      7               /1/7/10/    CD PLAYERS
11      1               /1/11/      2 WAY RADIOS
12      11              /1/11/12/   FRS

Do find all portables you simply use the Lineage from portables:

SELECT * FROM theTable WHERE Lineage LIKE '/1/7/%'

Cons:

• You need to do a UPDATE after each INSERT to append PK to Lineage

Suggestion:

I usally add another column where I put the path as text in (for instance 'electronics/televisions/tube')