recursive - mysql select tree parent

¿Cómo hacer la consulta SELECCIONAR recursiva en MySQL?

Question

Tengo una siguiente mesa:

col1 | col2 | col3
-----+------+-------
1    | a    | 5
5    | d    | 3
3    | k    | 7
6    | o    | 2
2    | 0    | 8

Si un usuario busca "1", el programa verá el col1 que tiene "1", luego obtendrá un valor en col3 "5", luego el programa continuará buscando "5" en col1 y obtendrá "3" en col3 , y así sucesivamente. Entonces se imprimirá:

1   | a   | 5
5   | d   | 3
3   | k   | 7

Si un usuario busca "6", imprimirá:

6   | o   | 2
2   | 0   | 8

¿Cómo construir una consulta SELECT para hacer eso?

Answer 1

Editar

La solución mencionada por @leftclickben también es efectiva. También podemos usar un procedimiento almacenado para el mismo.

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

Estamos utilizando la tabla temporal para almacenar los resultados de la salida y como las tablas temporales se basan en sesiones, no existirá ningún problema con respecto a que los datos de salida sean incorrectos.

SQL FIDDLE Demo

Prueba esta consulta:

SELECT 
    col1, col2, @pv := col3 as ''col3'' 
FROM 
    table1
JOIN 
    (SELECT @pv := 1) tmp
WHERE 
    col1 = @pv

SQL FIDDLE Demo :

| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |    a |    5 |
|    5 |    d |    3 |
|    3 |    k |    7 |

Nota
parent_id valor de child_id debe ser menor que el child_id para que esta solución funcione.

Answer 2

El procedimiento almacenado es la mejor manera de hacerlo. Porque la solución de Meherzad funcionaría solo si los datos siguen el mismo orden.

Si tenemos una estructura de tabla como esta

col1 | col2 | col3
-----+------+------
 3   | k    | 7
 5   | d    | 3
 1   | a    | 5
 6   | o    | 2
 2   | 0    | 8

No funcionará SQL Fiddle Demo

Aquí hay un código de procedimiento de muestra para lograr lo mismo.

delimiter //
CREATE PROCEDURE chainReaction 
(
    in inputNo int
) 
BEGIN 
    declare final_id int default NULL;
    SELECT col3 
    INTO final_id 
    FROM table1
    WHERE col1 = inputNo;
    IF( final_id is not null) THEN
        INSERT INTO results(SELECT col1, col2, col3 FROM table1 WHERE col1 = inputNo);
        CALL chainReaction(final_id);   
    end if;
END//
delimiter ;

call chainReaction(1);
SELECT * FROM results;
DROP TABLE if exists results;

Answer 3

La respuesta aceptada por @Meherzad solo funciona si los datos están en un orden particular. Resulta que funciona con los datos de la pregunta OP. En mi caso, tuve que modificarlo para que funcione con mis datos.

Nota Esto solo funciona cuando el "id" de cada registro (col1 en la pregunta) tiene un valor MAYOR QUE el "id padre" de ese registro (col3 en la pregunta). Este es a menudo el caso, porque normalmente el padre deberá crearse primero. Sin embargo, si su aplicación permite cambios en la jerarquía, donde un elemento puede volver a parentarse en otro lugar, entonces no puede confiar en esto.

Esta es mi consulta en caso de que ayude a alguien; tenga en cuenta que no funciona con la pregunta dada porque los datos no siguen la estructura requerida descrita anteriormente.

select t.col1, t.col2, @pv := t.col3 col3
from (select * from table1 order by col1 desc) t
join (select @pv := 1) tmp
where t.col1 = @pv

La diferencia es que table1 está siendo ordenado por col1 para que el padre lo col1 (ya que el valor de col1 del padre es más bajo que el del hijo).

Answer 4

La respuesta de Leftclickben funcionó para mí, pero quería un camino desde un nodo dado, una copia de seguridad del árbol hasta la raíz, y estos parecían ir hacia el otro lado, hacia abajo del árbol. Entonces, tuve que darle la vuelta a algunos de los campos y cambiar el nombre por claridad, y esto funciona para mí, en caso de que esto sea lo que los demás también quieren--

item | parent
-------------
1    | null
2    | 1
3    | 1
4    | 2
5    | 4
6    | 3

y

select t.item_id as item_id, @pv:=t.parent as parent
from (select * from item_tree order by item_id desc) t
join
(select @pv:=6)tmp
where t.item_id=@pv;

da:

item | parent
-------------
6    | 3
3    | 1
1    | null

Answer 5

Si desea poder tener un SELECT sin problemas de que el id principal deba ser menor que el id del hijo, se podría usar una función. También admite varios hijos (como debería hacer un árbol) y el árbol puede tener varias cabezas. También asegura romper si existe un bucle en los datos.

Quería usar SQL dinámico para poder pasar los nombres de tabla / columnas, pero las funciones en MySQL no son compatibles.

DELIMITER $$

CREATE FUNCTION `isSubElement`(pParentId INT, pId INT) RETURNS int(11)
DETERMINISTIC    
READS SQL DATA
BEGIN
DECLARE isChild,curId,curParent,lastParent int;
SET isChild = 0;
SET curId = pId;
SET curParent = -1;
SET lastParent = -2;

WHILE lastParent <> curParent AND curParent <> 0 AND curId <> -1 AND curParent <> pId AND isChild = 0 DO
    SET lastParent = curParent;
    SELECT ParentId from `test` where id=curId limit 1 into curParent;

    IF curParent = pParentId THEN
        SET isChild = 1;
    END IF;
    SET curId = curParent;
END WHILE;

RETURN isChild;
END$$

Aquí, la test la tabla debe modificarse para el nombre de la tabla real y las columnas (ParentId, Id) pueden tener que ajustarse para sus nombres reales.

Uso:

SET @wantedSubTreeId = 3;
SELECT * FROM test WHERE isSubElement(@wantedSubTreeId,id) = 1 OR ID = @wantedSubTreeId;

Resultado:

3   7   k
5   3   d
9   3   f
1   5   a

SQL para la creación de prueba:

CREATE TABLE IF NOT EXISTS `test` (
  `Id` int(11) NOT NULL,
  `ParentId` int(11) DEFAULT NULL,
  `Name` varchar(300) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

insert into test (id, parentid, name) values(3,7,''k'');
insert into test (id, parentid, name) values(5,3,''d'');
insert into test (id, parentid, name) values(9,3,''f'');
insert into test (id, parentid, name) values(1,5,''a'');
insert into test (id, parentid, name) values(6,2,''o'');
insert into test (id, parentid, name) values(2,8,''c'');

EDITAR: Aquí hay un fiddle para probarlo usted mismo. Me obligó a cambiar el delimitador usando el predefinido, pero funciona.