Getting all parent rows in one SQL query

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?

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'

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);

I think, there's no easy way to do that, using one query.

I would recommend to take a look at Nested Sets, that seems to fit your needs.

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

AFAIK no.

This Sitepoint article may help you.

You could retrieve all the elements with one query, store it in an array and then iterate, as explained here and here