how to get top parent from nested categories table?

Question

I'm trying to get the top parent id from category table:

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

There can be a lot of subcategories and my question is how to get the top parent of whatever the subcategory is?
For example, in database subcategory node can look like this:
parent>>subcategory>>subsubcategory>>subsubsubcategory
and another node can look like this:
parent>>subcategory

So if I query for top parent of subcategory with id=6, I get ELECTRONICS, etc.
How to get the top parent id with mysql? I hope that, this is fully understandable.

Not a built-in function. You need to write your own or find a function on the internet with similar behavior and then edit it. — Aurelio De Rosa, Nov 30 '11 at 10:51
I've found recursive function but it seems to be working on MSSQL and Oracle only :/ — sunpietro, Nov 30 '11 at 11:14
Duplicate of http://stackoverflow.com/questions/10187088/mysql-getting-the-top-level-parent-id-in-a-hierarchy — f.ardelian, Mar 11 '13 at 11:18

score 2 · Answer 1 · answered Dec 08 '11 at 13:07

You'll have to write a stored function that loops a query:

Query to find parent

SELECT parent FROM cat WHERE category_id = 6

Stored function to find top parent

DELIMITER $$

CREATE FUNCTION top_parent(PCat INTEGER) RETURNS INTEGER
BEGIN
  DECLARE MyParent INTEGER;
  DECLARE PrevParent INTEGER;
  SET PrevParent = PCat;
  REPEAT
    SELECT parent INTO MyParent FROM cat WHERE category_id = PrevParent;
    IF NOT(MyParent IS NULL) THEN
      SET PrevParent = MyParent;
    END IF;
  UNTIL (MyParent IS NULL) 
  END REPEAT;
  RETURN PrevParent;
END $$

DELIMITER ;

How to use the function in a query

SELECT name FROM cat WHERE category_id = top_parent(6)

score 1 · Answer 2 · answered Sep 28 '17 at 08:28

@r := 7 whatever the category_id you give here will get all of its parent and providing limit 1 will get the top-parent

SELECT T2.*
FROM (
       SELECT
         @r AS _category_id,
         (SELECT @r := parent FROM categories WHERE category_id = _category_id) AS parent,
         @l := @l + 1 AS lvl
       FROM
         (SELECT @r := 7, @l := 0) vars,
         categories WHERE @r <> 0) T1
  JOIN categories T2
    ON T1._category_id = T2.category_id
ORDER BY T1.lvl DESC
LIMIT 1;

how to get top parent from nested categories table?

2 Answers2