Getting all parent rows in one SQL query

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

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

In addition to the above solutions:

post
-----
id
title
author

author
------
id
parent_id
name


[post]

id  | title | author |  
----------------------
1   | abc   | 3      |


[author]

| id    | parent_id | name  |   
|---------------------------|
| 1     | 0         | u1    |
| 2     | 1         | u2    |
| 3     | 2         | u3    |
| 4     | 0         | u4    |

an author including parents can have an access to the post.

I want to check whether author has an access to the post.

Solution:

give the post author's id and return all its authors and author's parents

SELECT T2.id, T2.username 
FROM (
    SELECT @r AS _id, 
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
        @l := @l + 1
    FROM
        (SELECT @r := 2, @l := 0) vars, 
        users h     
    WHERE @r <> 0) T1 JOIN users T2 
ON T1._id = T2.id;

@r := 2 => assigning value to @r variable.

I used the previous answers as examples to make smth more readable.

SELECT  @org_id as id,
    (SELECT name FROM test.organizations WHERE id = @org_id) as name,
    (SELECT @org_id := parent_id FROM test.organizations WHERE id = @org_id) AS parent_id
FROM (SELECT @org_id := 4) vars, test.organizations org
WHERE @org_id is not NULL
ORDER BY id;

The result of execution looks like that:

(just for quick) to check it yourself you need to enter values from the question into database test, table organizations

CREATE TABLE organizations(
id        int(11) NOT NULL AUTO_INCREMENT,
name      varchar(45) DEFAULT NULL,
parent_id int(11)     DEFAULT NULL,
PRIMARY KEY (id));

insert into organizations values(1, "home", null);
insert into organizations values(2, "about", 1);
insert into organizations values(3, "contact", 1);
insert into organizations values(4, "legal", 2);
insert into organizations values(5, "privacy", 4);
insert into organizations values(6, "products", 1);
insert into organizations values(7, "support", 1);

If You have slug instead of id then simply run sub-query to find id of child category.
Table - categories
| id | parentId | slug |
|-------------------------|
| 1  |    0      |    u1    |
| 2  |    1      |    u2    |
| 3  |    2      |    u3    |
| 4  |    0      |    u4    |

SELECT T2.id, T2.slug
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parentId FROM categories WHERE id = _id) AS parentId,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := (SELECT id FROM categories WHERE slug = 'u3'), @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

While I was working on a solution for my own hierarchical table I looked at the WP multilevel category model. Based on the excellent answer provided here I made this query to get the parent categories in a Wordpress database. I'm not expert in the matter but this worked on my end and might be of help to someone looking for such answer.

  SELECT T2.term_id,T3.name,T3.slug
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent FROM wp_term_taxonomy WHERE term_id = _id AND taxonomy = 'category') AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 8, @p := 0, @l := 0) vars,
            wp_term_taxonomy h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN wp_term_taxonomy T2 ON T1._id = T2.term_id AND T2.taxonomy = 'category'
    LEFT JOIN wp_terms T3 ON T3.term_id = T2.term_id
    ORDER BY T1.lvl DESC