Is it possible to use one sql query to iterate through hierarchical table till NULL

Question

For example i have table that looks like this:

| ID (This is primary key) | parent (this refers to other rows in this table) |
|--------------------------|--------------------------------------------------|
| 1                        | NULL                                             |
| 2                        | 3                                                |
| 3                        | 1                                                |

Is it possible to have ONE mysql query which would return me all hierarchy of parents till it hits NULL?

For example if i would ask for row 2 parents, i want to get row 2, then row 3 and row 1. And if i would ask for row 3 parents, i want to get row 3 and row 1.

(By the way, I want my table to have multiple rows with NULL parents).

Is this possible, if yes how to do it?

No, not with that data representation. You can do so with a stored procedure or alternative data structure. — Gordon Linoff, Jul 03 '17 at 22:08

Bill Karwin · Accepted Answer · 2022-10-21T23:17:21.787

1

You can't do this easily until MySQL 8.0 recursive queries.

I did a presentation Recursive Query Throwdown in MySQL 8.0 for the Percona Live conference in April 2017, showing how to do this.

You can also see:

My older presentation Models for Hierarchical Data
My answer to What is the most efficient/elegant way to parse a flat table into a tree?
My book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming

edited Oct 21 '22 at 23:17

answered Jul 03 '17 at 22:08

Bill Karwin

538,548
86
673
828

It seems that I'm dealing with mysql 5.7. Gonna take a look at Adjacency list then. Thanks – Kudzė Jul 03 '17 at 22:22
Oh, while Looking at your slides i saw Path Enumeration, which is good for breadcrumbs, which I intend to use this table for. Marking this as best answer. Thanks. – Kudzė Jul 03 '17 at 22:29

score 0 · Answer 2 · answered Jul 03 '17 at 22:10

It is not possible to get the hierarchy from a single query in mysql using the structure you have chosen to represent your tree. You could switch to oracle (have a google for "oracle connect by") or use a different structure for your data (adjacency list) or run an indeterminant number of queries.

Is it possible to use one sql query to iterate through hierarchical table till NULL

2 Answers2