Getting all parent rows in one SQL query

Adapted from here:

SELECT T2.id, T2.nameFROM (    SELECT        @r AS _id,        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,        @l := @l + 1 AS lvl    FROM        (SELECT @r := 5, @l := 0) vars,        table1 h    WHERE @r <> 0) T1JOIN table1 T2ON T1._id = T2.idORDER BY T1.lvl DESC

The line @r := 5 is the page number for the current page. The result is as follows:

1, 'Home'2, 'About'4, 'Legal'5, 'Privacy'

Awesome answer by Mark Byers!

Maybe a bit late to the party, but if you also want to prevent an infinite loop when id = parent_id (i.e. somehow when data has been corrupted), you can expand the answer like this:

    SELECT T2.id, T2.name    FROM (        SELECT            @r AS _id,            @p := @r AS previous,            (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,            @l := @l + 1 AS lvl        FROM            (SELECT @r := 5, @p := 0, @l := 0) vars,            table1 h        WHERE @r <> 0 AND @r <> @p) T1    JOIN table1 T2    ON T1._id = T2.id    ORDER BY T1.lvl DESC

The Accepted answer has the best solution to retrieve all parent users of child user recursively. I have modified this as per my need.

For MySQL 5.5, 5.6 & 5.7

SELECT @r AS user_id,    (SELECT @r := parent_id FROM users_table WHERE id = user_id) AS parent_id,    @l := @l + 1 AS level    FROM (SELECT @r := 9, @l := 0) val, users_table WHERE @r <> 0 

Note : @r := 9. Where 9 is the child user's id.

See fiddle here

The above query is deprecated in MySQL 8. So here is the query for MySQL 8.0

with recursive parent_users (id, parent_id, level) AS (  SELECT id, parent_id, 1 level  FROM users_table  WHERE id = 9  union all  SELECT t.id, t.parent_id, level + 1  FROM users_table t INNER JOIN parent_users pu  ON t.id = pu.parent_id)SELECT * FROM parent_users;

Note : id = 9. Where 9 is the child user's id.

See fiddle here