Usuarios multinivel en la tabla Base de datos

Tengo una tabla de usuarios en la que tengo un campo 'id' y otro campo es 'parent id'. También he esperado el campo objective en la tabla de usuarios.

Tengo una list de usuarios hasta la jerarquía del octavo nivel. Donde A es padre de B y B es padre de C y así sucesivamente.

p.ej

A level 0 | B level 1 | c level 2 

Ahora cuando estoy buscando al usuario A. Quiero get todos los usuarios secundarios usando la consulta sql 'objective esperado'. es decir, cuando uso id = id de A entonces puedo ver el objective esperado de A, B, C, etc.

Si expected_targets para A, B y C son 1000, 500, 200 respectivamente, la salida debería ser como:

 id parent_id expected_target A_id 1000 B_id A_id 500 C_id B_id 200 

esto hará el trabajo – 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) 
 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 ; 

SALIDA:

 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) 

ACTUALIZACIÓN: si no quieres el tree completo, el tree verdadero y nada más que el tree, pero solo una parte del subtree, por supuesto puedes cambiar las condiciones un poco:

 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 ; 

Como esto está labeldo con 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 no está lo suficientemente avanzado como para soportar esto. Allí necesitarás hacer una auto-unión para cada nivel.