Skip to content

Instantly share code, notes, and snippets.

@Korol

Korol/all_parent_rows_in_one_SQL_query.sql

Last active March 27, 2020 11:36
Show Gist options
  • Save Korol/00fc78ea6f79f6e54291f595bebd6ebb to your computer and use it in GitHub Desktop.
Save Korol/00fc78ea6f79f6e54291f595bebd6ebb to your computer and use it in GitHub Desktop.
Download ZIP
Getting all parent rows in one SQL query (FOR BREADCRUMBS) http://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query
Raw
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;
@vkponomarev
Copy link

vkponomarev commented Mar 26, 2020 •
edited
Loading

Hello, this works perfect. But i need a little advise:

i have 2 tables

1: pages

id  name    parent_id   url

1   Home         0       (some url)
2   About         1       (some url)
3   Contact      1       (some url)
4   Legal          2       (some url)
5   Privacy       4       (some url)
6   Products    1       (some url)
7   Support     1        (some url)

2: pages_text

id  name                          pages_id   languages_id

1   Главная                       0           2
2   About                          1           1
3   (different languges)    1            3
4   (different languges)    2            4
5   (different languges)    4            2
6   (different languges)    1            1
7   (different languges)    1            2

So i need to get full chain with pages.url and pages_text.name where languages_id = 2

Can u help me? Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment