Need help to query all users under parent id

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



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

Thank you in advance

What have you tried so far?

I tried inner join both table but it did not solve my problem,.

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 tried but I could not get the idea.I am confuse on how to solve this problem.

Thank you in advance.

I tried this

SELECT `g`.`p_id`,
       `u`.* FROM `tree_table` as `g` 
         LEFT JOIN `user_table` as `u` 
         ON `g`.`u_id` = `u`.`user_id` WHERE `g`.`p_id` >= 5

seems it works but if I change it to >=6 it will display 9 and 10 record but 6 is not the parent of 9 and 10.

@r937 , @DaveMaxwell

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';

I think my tree_table is wrong…

Did you look at any of the examples that the searches I suggested earlier returned? Not a one will allow you to do it in one single query.

You’re either going to have to use your server side language for looping or a stored proc of some kind.

Is my tree_table is correct ?,I’m still reading other links that might help me.

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.

[quote=“jemz, post:7, topic:106770, full:true”]

I don’t know if this right way of doing sql statment[/quote]

  1. what happened when you tested it? ™

  2. what are the L/R position values for?

1 Like


The L/R position, is to determine that this (member/user) under parent id “5” is on his Left postion or on his Right position.

I can draw this visual tree by using google organizatinal chart

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.

Thank you in advance.

try this –

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


It works thank you,…I will try to add this in query

, t1.position
, t2.position
, t3.position
, t4.position

so that i can show there position. :slight_smile:


The result is okay,but I am confuse on how can I draw the visual tree because it has 4 columns.

lev1     lev2       lev3     lev4

5         7            9        null

5        7            10       null

5         8           null      null

Thank you in advance.

the query works as expected, the results are correct, and that’s as far as i can help you

@r937 ,

Yup it works :smile:, I will just find way to work this in visual tree.

Thank you for helping me.


Is it possible to have output like this

p_id            user_id                 position

5                 7                         L

5                 8                         R

7                 9                          L

7                 10                         R

Thank you in advance.


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
    user_id , 
    0 AS HLevel,
FROM CTEmytree
WHERE p_id = 0


    (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


Thank you in advance.