如何从sql表中查找子父树

I have a table child where I have below structure:

我有一个桌子，我的结构如下：

id  child_name  parent_id   parent_name
1   vikas        2           sunny
2   john         3           seema
3   ajay         4           delhi
4   josh         4           main

I am trying to find child to parent till parent_name matches to main, then I will stop. I need single query sql if it is possible.

我试图找到child to parent，直到parent_name匹配main，然后我将停止。如果可能，我需要单个查询sql。

I tried this but it is giving me only one level.

我试过这个，但它给了我一个级别。

SELECT ChildUserType.child_name, 
       ChildUserType.parent_name,       
       ParentUserType.id, 
      ParentUserType.parent_id
FROM groups AS ChildUserType
  LEFT JOIN groups AS ParentUserType 
         ON ChildUserType.id = ParentUserType.parent_id 
where ChildUserType.id='1';

1 个解决方案

#1

You can try this :

你可以试试这个：

CREATE PROCEDURE find_ancestry(IN id_init int)
 BEGIN
 DECLARE child_id int;
 DECLARE prev_id int;
 SET prev_id = id_init;
 SET child_id=0;
 SELECT parent_id into child_id 
 FROM groups WHERE id=id_init ;
 create TEMPORARY  table IF NOT EXISTS temp_table as (select * from groups where 1=0);
 truncate table temp_table;
 WHILE child_id <> 0 DO
   insert into temp_table select * from groups WHERE id=prev_id;
   SET prev_id = child_id;
   SET child_id=0;
   SELECT parent_id into child_id
   FROM groups WHERE id=prev_id;
 END WHILE;
 select * from temp_table;
 END //