Multilevel users in a database table

Question

I have one table user in which I have one id field and another field is parent id. I also expected the target field in the user table.

I have a list of users up to level 8. Where A is the parent of B and B is the parent of C, etc.

eg

A level 0 | B level 1 | c level 2 

Now that I am looking for user A. I want all subusers to use the "expected target" SQL query. those. when I use id = id from A, then I see the expected target of A, B, C, etc.

If the expected_targets for A, B, and C are 1000, 500, 200, respectively, the output should look like this:

 id parent_id expected_target A_id 1000 B_id A_id 500 C_id B_id 200 

Answer 1

this will do the job - http://sqlfiddle.com/#!2/0de1f/7 :

 select u1.id, u1.parent_id, u1.expected_target from users u1 left join users u2 on u1.parent_id = u2.id left join users u3 on u2.parent_id = u3.id left join users u4 on u3.parent_id = u4.id left join users u5 on u4.parent_id = u5.id left join users u6 on u5.parent_id = u6.id left join users u7 on u6.parent_id = u7.id left join users u8 on u7.parent_id = u8.id where :A_id in (u1.id, u2.id, u3.id, u4.id, u5.id, u6.id, u7.id, u8.id, u8.parent_id) 

Answer 2

 SET search_path='tmp'; DROP TABLE targets CASCADE; CREATE TABLE targets ( id integer not null primary key , parent_id integer references targets(id) , expected_target integer ); INSERT INTO targets(id,parent_id,expected_target) VALUES (1,NULL, 1000), (2,1, 500), (3,2, 200); WITH RECURSIVE zzz AS ( SELECT t0.id, t0.parent_id , 0::integer AS level , t0.expected_target FROM targets t0 WHERE t0.parent_id IS NULL UNION SELECT t1.id, t1.parent_id , 1+zzz.level AS level , t1.expected_target FROM targets t1 JOIN zzz ON zzz.id = t1.parent_id ) SELECT * FROM zzz ; 

OUTPUT:

 SET DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "targets_pkey" for table "targets" CREATE TABLE INSERT 0 3 id | parent_id | level | expected_target ----+-----------+-------+----------------- 1 | | 0 | 1000 2 | 1 | 1 | 500 3 | 2 | 2 | 200 (3 rows) 

UPDATE: if you do not want the whole tree, the true tree and nothing but the tree, but only part of its subtree, you can, of course, change the conditions a bit:

 WITH RECURSIVE zzz AS ( SELECT t0.id, t0.parent_id , 0::integer AS level , t0.expected_target FROM targets t0 -- WHERE t0.parent_id IS NULL WHERE t0.id = 2 UNION SELECT t1.id, t1.parent_id , 1+zzz.level AS level , t1.expected_target FROM targets t1 JOIN zzz ON zzz.id = t1.parent_id ) SELECT * FROM zzz ; 

Answer 3

Since this is flagged by PostgreSQL:

 with recursive users_tree as ( select id, parent_id, expected_target, 1 as level from users where id = 'A_id' union all select c.id, c.parent_id, c.expected_target, p.level + 1 from users c join users_tree p on c.parent_id = p.id ) select * from users_tree 

MySQL is not well developed to support this. There you need to make an independent connection for each level.