MySQL hierarchical recursive query [требует правки]

Так как нам надо двигаться по дереву от потомка к родителю, то на каждом уровне иерархии у нас добавляется всего одна запись. Так что нам вообще не нужна рекурсия, а нужен цикл который на каждом шаге получит родителя текущей записи.

Циклов в SQL не бывает, так что будем его эмулировать с помощью произвольной таблицы (или запроса) который выдаст количество строк, достаточное для получения всех родителей. В данном примере я решил в качестве такого курсора использовать уникальные parent_staff из таблицы, так как их заведомо не меньше, чем нужно итераций "цикла". Если известна максимально возможная глубина иерархии лучше (для быстродействия) ограничить его limit.

select distinct id
  from (
    select @id:=(select parent_staff from staff where staff_id=@id) id
      from (select distinct parent_staff from staff) as curs,
           (select @id:=2 /* стартовый staff_id */ ) A
) B

with recursive tree (TABLE, staff_id, level, pathstr)
as (select TABLE, staff_id, 0, cast('' as text) 
   from tree_sample
   where staff_parent_staff is null 
union all
   select tree_sample.TABLE, tree_sample.staff_id, t.level + 1, tree.pathstr + tree_sample.TABLE
   from tree_sample 
     inner join tree on tree.staff_id = tree_sample.staff_parent_staff) 
select staff_id, space( level ) + TABLE as TABLE 
from tree 
order by pathstr

Рекурсивных запросов в stable у MySQL пока нет, так что только ХП.

С другой стороны, рекурсия тут не нужна, от слова "совсем". Схематичненько ежели, то будет где-то так:

CREATE PROCEDURE get_all_parents(IN child_id BIGINT)
BEGIN
    DECLARE cnt BIGINT DEFAULT 0;
    DROP TEMPORARY TABLE IF EXISTS tmp_parents;
    CREATE TEMPORARY TABLE tmp_parents(id BIGINT UNIQUE) ENGINE=Memory;
    INSERT INTO tmp_parents(id) SELECT child_id;
    WHILE (SELECT COUNT(*) FROM tmp_parents) > cnt
        SELECT COUNT(*) INTO cnt FROM tmp_parents;
        INSERT IGNORE INTO tmp_parents(id)
            SELECT parent_id 
            FROM datatable 
            WHERE id IN (
                        SELECT id FROM tmp_parents
                        );
    END WHILE;
    /* DELETE FROM tmp_parents WHERE id = child_id; */
    SELECT id FROM tmp_parents;
    DROP TEMPORARY TABLE tmp_parents;
END;