¿Cómo hacer la consulta de SELECCIÓN recursiva en MySQL?

La respuesta aceptada por @Meherzad solo funciona si los datos están en un orden particular. Sucede que funciona con los datos de la pregunta OP. En mi caso, tuve que modificarlo para trabajar 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 tendrá que ser creado primero. Sin embargo, si su aplicación permite cambios en el jerarquía, donde un elemento puede ser re-parentado en otro lugar, entonces no se 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 esté después de él (ya que el valor del padre col1 es menor que el del hijo).

La respuesta de Leftclickben funcionó para mí, pero quería un camino desde un nodo dado hasta la raíz del árbol, y estos parecían ir hacia el otro lado, hacia abajo del árbol. Por lo tanto, tuve que voltear algunos de los campos alrededor y renombrado para la claridad, y esto funciona para mí, en caso de que esto es lo que alguien más quiere demasiado {

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

El procedimiento almacenado es la mejor manera de hacerlo. Porque la solución de Meherzad solo funcionaría 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 ejemplo 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;

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

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

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 tabla test tiene que ser modificada al nombre real de la tabla 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 pruebas:

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 violín para probarlo usted mismo. Me obligó a cambiar el delimitador usando el predefinido, pero funciona.