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

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     Name1       null            /1/         ELECTRONICS2       1               /1/2/       TELEVISIONS3       2               /1/2/3/     TUBE4       2               /1/2/4/     LCD5       2               /1/2/5/     PLASMA6       6               /1/6/       GAME CONSOLES7       1               /1/7/       PORTABLE ELECTRONICS8       7               /1/7/8/     MP3 PLAYERS9       8               /1/7/8/9/   FLASH10      7               /1/7/10/    CD PLAYERS11      1               /1/11/      2 WAY RADIOS12      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')

Something like this (ANSI SQL):

WITH RECURSIVE emptree (userid, name, managerid) AS (    SELECT userid,            name,            managerid    FROM the_table     WHERE userid = 2    UNION ALL    SELECT c.userid,            c.name,           c.managerid    FROM the_table c       JOIN emptree p ON p.userid = c.managerid)SELECT *FROM emptree

In my opinion, the problem with the adjacency list model is that it gets difficult to deal with in SQL especially when you don't know how deeply nested your tree structure is going to be.

The 'left and right leaf way' you mention is probably the nested set model and allows you to store things like this

LFT   RGT   Name1     8      nilesh2     7      nikhil3     4      nitin5     6      Ruchi

Then you can find all of anyones subordinates by simply

SELECT Name FROM Hierarchy WHERE LFT BETWEEN @LFT AND @RGT

I think it is much easier to deal with for querying but is harder to do for tree modifications. If your data doesn't change much then I think this is a much better solution. (Not everyone will agree with me though)

There is a Very good Tutorial here