Finding all parents in mysql table with single query (Recursive Query)

Question

I have this schema

Sample Data

| ID |             TITLE | CONTROLLER |            METHOD | PARENT_ID |
|----|-------------------|------------|-------------------|-----------|
|  1 |         Dashboard |      admin |         dashboard |         0 |
|  2 |           Content |      admin |           content |         0 |
|  3 |           Modules |      admin |           modules |         0 |
|  4 |             Users |      admin |             users |         0 |
|  5 |          Settings |      admin |          settings |         0 |
|  6 |           Reports |      admin |           reports |         0 |
|  7 |              Help |      admin |              help |         0 |
|  8 |             Pages |    content |             pages |         2 |
|  9 |             Media |    content |             media |         2 |
| 10 |          Articles |    content |          articles |         2 |
| 11 |            Menues |    content |            menues |         2 |
| 12 |         Templates |    content |         templates |         2 |
| 13 |            Themes |    content |            themes |         2 |
| 14 |              Blog |    content |              blog |         2 |
| 15 |             Forum |    content |             forum |         2 |
| 16 |      Core Modules |    modules |       core_module |         3 |
| 17 |      User Modules |    modules |       user_module |         3 |
| 18 |         All Users |      users |         all_users |         4 |
| 19 |            Groups |      users |            groups |         4 |
| 20 |       Permissions |      users |       permissions |         4 |
| 21 | Import and Export |      users |     import_export |         4 |
| 22 |        Send Email |      users |         send_mail |         4 |
| 23 |     Login Records |      users |     login_records |         4 |
| 24 |  General Settings |   settings |  general_settings |         5 |
| 25 |    Email Settings |   settings |    email_settings |         5 |
| 26 |   Popular Content |    reports |   popular_content |         6 |
| 27 | Most Active Users |    reports | most_active_users |         6 |
| 28 |     Documentation |       help |     documentation |         7 |
| 29 |             About |       help |             about |         7 |
| 30 |          Products |   products |           product |        17 |
| 31 |        Categories | categories |          category |        17 |

SQL Fiddle demo.I have inserted some sample data.

Challange

I need to find all the parents of the record where the title is Categories. How can I get all the parents with only a single query?
I mean I need this result:

Desired Output

id | title        |  controller  | method      | url     | parent_id 
----------------------------------------------------------------  
3  | Modules      |   admin      | modules     | (NULL)  | 0           
17 | User Modules |   modules    | user_module | (NULL)  | 3           
31 | Categories   |   categories | category    | (NULL)  | 17

Let suppose I want to fetch an entry with all of its parent and I want to use the where condition id = 31, it should fetch above records.

score 77 · Accepted Answer · edited Mar 05 '13 at 11:20

77

SELECT T2.id, T2.title,T2.controller,T2.method,T2.url
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r <> 0) T1
JOIN menu T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

Demo

edited Mar 05 '13 at 11:20

Muhammad Raheel

19,823
7
67
103

answered Oct 18 '12 at 06:19

sel

4,982
1
16
22

3

Well this is great but can you please explain step by step in your answer how you manage to do this. And what are the points in your mind that forced you to achieve this. – Muhammad Raheel Oct 18 '12 at 06:27
2

Suggest you to run the subquery only first to see what is the result achieved as the subquery is the main components in this query. In this way, might help to understand how this query work. – sel Oct 18 '12 at 06:30
Thanks for your feedback. really help full i even read some books like Mysql antipattren and trees in mysql but couldn't find a solution for this problem your method is simple and useful. Thanks again – Muhammad Raheel Oct 18 '12 at 06:41
Actually i read this somewhere else and use it in my app. Just managed to dig back the article i read before. FYR, http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ This is indeed a great and simple solution. – sel Oct 18 '12 at 06:54
Is it possible to set @r to outer_menu.parentid? Trying to do a select outer_menu.title, (select group_concat(menu_title) .... @r=outer_menu.parent_id) as vars) FROM menu outer_menu – jontro Sep 28 '16 at 15:04
3

How to get its inverse I mean all childs by parent_id – Zohaib Nov 14 '17 at 11:33
This seems to be not working when one node has more than one parent. – callofdutyops Jan 13 '20 at 10:53
1

``Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.`` I have this error under MYSQL 8, can someone help me? – Rocstar Jan 11 '21 at 15:17
1

This is lo longer working in MySQL 8. For MySQL 8 https://stackoverflow.com/a/67439977/6133016 – MaYaNk May 09 '21 at 03:34

Finding all parents in mysql table with single query (Recursive Query)

1 Answers1

Linked