I need your help please.
I have this table how can I get all the u_id,that are under p_id as there parent id,
example in tree_table I want to get the parent_id 5 ,his children(u_id) are 7,8,9,10 ,…
9 and 10 is still his children eventhough there parent id is 7 but 7 is a child of parent id 5.
Is this possible ?
here is the output I want
users usernames p_id
7 miller 5
8 jamie 5
9 honey 7
10 jack 7
**tree_table**
p_id user_rid position
null 1
1 5 L
1 6 R
5 7 L
5 8 R
7 9 L
7 10 R
**user table**
user_id user_name
1 millan
2 john
3 max
4 chris
5 jane
6 lester
7 miller
8 jamie
9 Honey
10 Jack
A search engine query for “treeview query” should return several examples to get you started. If you look and those and get a basic framework in place but it’s not getting you quite what you want, let us know.
I don’t know if this right way of doing sql statment,please correct me If I am wrong.
SELECT t1.user_id AS lev1, t2.user_id as lev2, t3.user_id as lev3, t4.user_id as lev4
FROM tree_table AS t1
LEFT JOIN tree_table AS t2 ON t2.p_id = t1.user_id
LEFT JOIN tree_table AS t3 ON t3.p_id = t2.user_id
LEFT JOIN tree_table AS t4 ON t4.p_id = t3.user_id
WHERE t1.p_id = '5';
The below post provides an algorithm to parse an adjacency list using the application language to filter children. Use the primary key of the user for the first argument of the convertAdjacencyListToTree function instead of of the root id specified in the post (null). That should get you a series of nested forming a tree from the specified user. However, you will need to select the entire tree in SQL and let the application language handle filtering. Therefore, if there is a lot of data loading it all into memory and filtering as shown might not be the *best route.
by using the parentid and the userid,I am just stuck to show what are the donwlines or children when I click a specific member in a visual tree. example I will click user “5”
it should display only 5 is the root and his children 7,8,9 and 10.,this is my problem I am confuse how can I make query to produce this following users.
SELECT t1.user_id AS lev1
, t2.user_id as lev2
, t3.user_id as lev3
, t4.user_id as lev4
FROM tree_table AS t1
LEFT OUTER
JOIN tree_table AS t2
ON t2.p_id = t1.user_id
LEFT OUTER
JOIN tree_table AS t3
ON t3.p_id = t2.user_id
LEFT OUTER
JOIN tree_table AS t4
ON t4.p_id = t3.user_id
WHERE t1.user_id = 5
How can I make it work this in MySQL, I found here Using CTE
here is the code I modified ,but I could not run this in mysql editor.
;WITH CTEmytree
AS
(
SELECT
p_id,
user_id ,
0 AS HLevel,
CAST(RIGHT(REPLICATE('_',5) + CONVERT(VARCHAR(20),p_id),20) AS VARCHAR(MAX)) AS OrderByField
FROM CTEmytree
WHERE p_id = 0
UNION ALL
SELECT
C.p_id,
C.user_id,
(CTE.HLevel + 1) AS HLevel,
CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) + CONVERT(VARCHAR(20),C.ID),20) AS VARCHAR(MAX)) AS OrderByField
FROM CTEmytree C
INNER JOIN CTEgentree CTE ON CTE.user_id = C.p_id
WHERE C.p_id IS NOT NULL
)
, I will just find way to work this in visual tree.