Geek Answers Handbook

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

table - user

columns - (userId, name, managerId)

rows -

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

if I give the user ID, he should list him all the users of the reporting. 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 an effective way to manage the tree structure in the database? What about left and right sheet?

+4
source share
4 answers

In my opinion, the problem with the adjacency list model is that SQL is difficult to handle, especially if you don’t know how deeply your tree structure will be.

The "left and right leaf path" that you mention is probably a nested dialing model and allows you to store things like this.

 LFT RGT Name 1 8 nilesh 2 7 nikhil 3 4 nitin 5 6 Ruchi

Then you can find all subordinates of anyones just

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

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

There is a very good study guide here

+5
source

I am using a text box to process trees in SQL. This is easier than using left / right values.

Let's take an example from a MySQL article:

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

This will 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

Find all portable devices that you simply use Lineage from portable devices:

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

Minuses:

  • You need to do UPDATE after each INSERT to add PK to Lineage

Sentence:

I usually add another column in which I put the path as text in (e.g. 'electronics/televisions/tube' )

+11
source

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
+8
source 
 SELECT user.id FROM user WHERE user.managerid = 2

Is this what you want?

-1
source

More articles:


All Articles