How Can I Find All Parent Records in a MySQL Table Using a Single Recursive Query?

Finding Parents in a MySQL Table with Recursion

In a database schema, establishing hierarchical relationships is common. When querying such data, it becomes necessary to retrieve not only specific records but also their hierarchical connections. This article will address the task of finding all parents of a record within a MySQL table using a single query.

Problem Statement

Given a table with columns ID, TITLE, CONTROLLER, METHOD, and PARENT_ID, find all the parents of a record where the TITLE is "Categories" using a single SQL query.

Desired Output

The output should display the following columns:

• id
• title
• controller
• method
• url
• parent_id

and should fetch the records:

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

Solution

To achieve the desired output, we need to leverage MySQL's recursive query capabilities. Here's the SQL query we can use:

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;

Let's break down this query:

• The inner query (SELECT @r AS _id, ...) generates a hierarchy of records by recursively selecting the PARENT_ID and incrementing the level.
• The outer query (SELECT T2.id, ...) joins the inner query with the original table menu (renamed to T2) to obtain the desired data.
• The ORDER BY clause sorts the results by level in descending order.

By executing this query, we can retrieve all the parents of the record with TITLE as "Categories" in a single database call.

The above is the detailed content of How Can I Find All Parent Records in a MySQL Table Using a Single Recursive Query?. For more information, please follow other related articles on the PHP Chinese website!