Getting all parent rows in one SQL query

Question

I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

I'm attempting to make a breadcrumb trail. So i have the 'id' of the child, I want to get all available parents (iterating up the chain until we reach 0 "Home"). There could be any number or child rows going to an unlimited depth.

Currently I am using an SQL call for each parent, this is messy. Is there a way in SQL to do this all on one query?

Answer 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' 

Answer 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

Answer 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

Answer 4

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.