laurentiuilie · October 31, 2017 09:40
diff --git a/all_parent_rows_in_one_SQL_query.sql b/all_parent_rows_in_one_SQL_query.sql
 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:
 Adapted from here: https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/
 For category "Privacy" (id=5):
 ----
 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 ID for the current category. 
 The result is as follows:
 ----
 1, 'Home'
 2, 'About'
 4, 'Legal'
 5, 'Privacy'

 FOR MY TASK:
 SELECT T2.id, T2.title
 FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM categories WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 87, @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
 JOIN categories T2
 ON T1._id = T2.id
 ORDER BY T1.lvl DESC;
	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:
	Adapted from here: https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/
	For category "Privacy" (id=5):
	----
	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 ID for the current category.
	The result is as follows:
	----
	1, 'Home'
	2, 'About'
	4, 'Legal'
	5, 'Privacy'

	FOR MY TASK:
	SELECT T2.id, T2.title
	FROM (
	SELECT
	@r AS _id,
	(SELECT @r := parent_id FROM categories WHERE id = _id) AS parent_id,
	@l := @l + 1 AS lvl
	FROM
	(SELECT @r := 87, @l := 0) vars,
	categories h
	WHERE @r <> 0) T1
	JOIN categories T2
	ON T1._id = T2.id
	ORDER BY T1.lvl DESC;