How to get all children of a node in 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 id of user it should list all reporting people to him . if I give userId = 2 it sh开发者_运维技巧ould 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 ?
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'
)
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 Name
1 8 nilesh
2 7 nikhil
3 4 nitin
5 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
I have a simple answer to this question:
Table creation:
Create Table #AllChilds(id int)
List item:
Declare @ParentId int;
set @ParentId=(Select Id from Employee Where id=1)
-- Here put the id as the record for which you want all childs
While(@ParentId is not null )
begin
set @ParentId=(Select Id from Employee Where ParentId=@ParentId)
insert into #AllChilds values(@ParentId)
end
See the result:
Select Id from #AllChilds
Cleanup:
Drop table #AllChilds
SELECT user.id FROM user WHERE user.managerid = 2
Is this what you want?
精彩评论