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

Revisions

  1. Korol revised this gist Sep 7, 2016. No changes.
  2. Korol revised this gist Sep 7, 2016. No changes.
  3. Korol revised this gist Sep 7, 2016. No changes.
  4. Korol revised this gist Sep 7, 2016. 1 changed file with 16 additions and 1 deletion.
    17 changes: 16 additions & 1 deletion all_parent_rows_in_one_SQL_query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -39,4 +39,19 @@ The result is as follows:
    1, 'Home'
    2, 'About'
    4, 'Legal'
    5, 'Privacy'
    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;
  5. Korol renamed this gist Sep 7, 2016. 1 changed file with 0 additions and 0 deletions.
    0 query.sql → all_parent_rows_in_one_SQL_query.sql
    View file
    File renamed without changes.
  6. Korol created this gist Sep 7, 2016.
    42 changes: 42 additions & 0 deletions query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    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'