Getting all parent rows in one SQL query

Solution 1:

Adapted from here:

SELECT T2.id, T2.name
FROM (
    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) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER 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'

Solution 2:

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

Solution 3:

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