how to list out all uplines?

Question

I want to select "Apple" then output "John" and "Michelle". I try to run the code but it is not working. Please help.

select @start := title as 'title', id, parent
from mytable
join 
(select @start := 0) temp
where parent = @start and title = "Apple";

Link : http://sqlfiddle.com/#!9/91fd40/1/0

`hhttp://sqlfiddle.com/#!9/91fd40/21` try this. – VIKAS KATARIYA Jan 01 '20 at 10:28 — VIKAS KATARIYA, Jan 01 '20 at 10:28

score 0 · Answer 1 · answered Jan 01 '20 at 15:54

I found the answer myself just in case anyone need it Demo :

SELECT T2.id, T2.title, T2.parent
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent FROM mytable WHERE id = _id) AS parent,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 6, @l := 0) vars,
        mytable h
    WHERE @r <> 0) T1
JOIN mytable T2
ON T1._id = T2.id 
where T2.title<>'sky'
ORDER BY T1.lvl DESC

how to list out all uplines?

1 Answers1