CODEWITHSUNDEEP
mysqlsql
Paul
Paul

Reputation: 421

sql - Get top parent of specified record

I have a standard nested category tree:

| id | parent_id |      name      |
+----+-----------+----------------+
|  1 |     0     |   Category 1   |
|  2 |     0     |   Category 2   |
|  3 |     0     |   Category 3   |
|  4 |     1     |  Category 1.1  |
|  5 |     1     |  Category 1.2  |
|  6 |     2     |  Category 2.1  |
|  7 |     2     |  Category 2.2  |
|  8 |     7     | Category 2.2.1 |

and now I need to get top parent of specified item so I do:

SELECT
    cat.*
FROM
    categories cat
LEFT JOIN
    categories subCat
    ON
        subCat.parent_id = cat.id
        AND cat.parent_id = 0
WHERE
    subCat.id = 5;

and if item is first-level child, it's working ok but is item is second-level child (eg. 8) I'm not getting records - how to do this?
Here is SQlFiddle: http://sqlfiddle.com/#!9/5879bd/11

UPDATE Here is real example: http://sqlfiddle.com/#!9/6f1d1c/1
I want to get parent category of Xiaomi

Upvotes: 1

Views: 282

Answers (2)

sticky bit
sticky bit

Reputation: 37487

With MySQL 5.6 you cannot use recursive CTEs.

To do it properly, for an arbitrary tree depth, you need to write a function/procedure, that traverses the hierarchy and returns the top node once reached.

As a workaround, when the maximum number of level d is set, you can left join the parent (d - 1) times. Use coalesce() to get the first non null value along the path. So in your case, for d = 3:

SELECT c.*
       FROM categories c
            INNER JOIN (SELECT coalesce(c3.id, c2.id, c1.id) id
                               FROM categories c1
                                    LEFT JOIN categories c2
                                              ON c2.id = c1.parent_id
                                    LEFT JOIN categories c3
                                              ON c3.id = c2.parent_id
                               WHERE c1.id = 10) t
                       ON t.id = c.id;

(I first select the ID of the top node and inner join the rest, to avoid coalesce() on all columns. It might give a false result on nullable columns if the value for the column in the top node is null but not for any child node. It should display NULL then, but will falsely show the non value from the child node.)

But note: It will fail if the depth grows!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This answers the original version of the question.

To get the top level, you can use the name column:

SELECT c.*
FROM categories c JOIN
     categories sc
     ON sc.id = 10 AND
        c.name = SUBSTRING_INDEX(sc.name, '.', 1);

Upvotes: 0

Related Questions