Finding all parents in mysql table for all records with single query (Recursive Query)

Asked Nov 01 '20 at 06:31

Active Nov 01 '20 at 06:31

Viewed 32 times

I have table [category], which has fields [id, name, parent_id] - hierarchical data. I have this hierarchical query which find all parents for child with id=259:

SELECT  _id AS id, parent_id,
        @cl := @cl + 1 AS level
FROM    (
        SELECT  @r AS _id,
                    (
                SELECT  @r := parent_id
                FROM    category
                WHERE   id = _id
                ) AS parent_id,
                @l := @l + 1 AS level
        FROM    (
                SELECT  @r := 259,
                        @l := 0,
                        @cl := 0
                ) vars,
                category h
        WHERE   @r <> 0
        ORDER BY
                level DESC
        ) qi

So my questions are:

How to retrieve parents for all records from the table [category], not only for the record with id=259?
How to include name field in this record set?

asked Nov 01 '20 at 06:31

Dmitrijs

what does `select version();` say? – ysth Nov 01 '20 at 06:36
@ysth - 5.7.24. – Dmitrijs Nov 01 '20 at 06:59
1

Any option to upgrade? – Strawberry Nov 01 '20 at 07:58
Does it mean there no solution without upgrade? – Dmitrijs Nov 01 '20 at 08:56
There are solutions without upgrade (see answers to the duplicate question - not just the accepted one!), however you need wrapper functions or procedures to make those work for the entire table. With a recursive CTE in mysql v8.0, it is a lot simpler task. – Shadow Nov 01 '20 at 09:36
What about second question: How to include name field in this query? – Dmitrijs Nov 01 '20 at 18:02

Finding all parents in mysql table for all records with single query (Recursive Query)

0 Answers0