multi user level with parent id in MySQL

Question

I have this 4 level user table as below

user_id parent_id
1       NULL
2       1
3       2
4       2
5       3
6       1

I would like to select all user under a certain parent id, for example, user_id = 1 and in this case shall return result like below:

user_id  parent_id
2        1         // return because parent_id = 1
6        1         // return because parent_id = 1
3        2         // return because parent_id of 2 is 1
4        2         // return because parent_id of 2 is 1
5        3         // return because parent_id of 3 is 2

By providing an id, what would be the query?

Thanks in advance!

check this :: http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries — Sashi Kant, Dec 04 '12 at 07:26

score 0 · Accepted Answer · answered Dec 04 '12 at 07:29

0

Try this ::

    Select 
user_id 
from 

my_table 
where parent_id in 
(
Select 
user_id 
from my_table where parent_id in 
(Select user_id from my_table where parent_id in(Select user_id from my_table where parent_id =?)))

answered Dec 04 '12 at 07:29

Sashi Kant

13,277
9
44
71

score 0 · Answer 2 · edited Mar 26 '13 at 08:35

0

parentid as refid 
Select userid  from SampleTree where refid = 0 or refid in (Select userid from SampleTree where refid =0 
or refid in (Select userid from SampleTree where refid =0 or
refid in (Select userid from SampleTree where refid =0)))

edited Mar 26 '13 at 08:35

Baby Groot

4,637
39
52
71

answered Mar 26 '13 at 08:13

francis_jaba

9
1

multi user level with parent id in MySQL

2 Answers2