MySQL find all parents recursively

Question

I have the following tables:

Category
id INT
name VARCHAR

Category_parent
category_id INT
parent_id INT

In Category_parent I store category relationship, and both columns get id from category table. So I can identify what is parent of what.

There can be any number of generations, so it is a bit difficult to find all categories from which a particular category inherited.

For example, CAT10's parent is CAT5, CAT5's CAT3, CAT3's CAT1 and so on. Besides, one category may have any number of parents. I give just name of category and my query should return all ancestors of that category.

Is there a solution to such problems in MySQL? How can I use stored procedure for this?

See this one: https://stackoverflow.com/questions/7569399/get-a-recursive-parent-list — vhu, Apr 17 '14 at 12:29
MySQL does not offer support for recursive queries or data structures. You can do what you want with a stored procedure or with an alternative data structure. — Gordon Linoff, Apr 17 '14 at 12:33

score 0 · Answer 1 · answered Apr 17 '14 at 14:31

Let me give you an idea. Create a function that gets the parent_id from a given ID a number of times, let's call it generation. Cat10 generation 1 would be parent who is CAT5 generation 2 would be CAT3 and so on.

DROP FUNCTION IF EXISTS getNameIdGeneration;
DELIMITER $$
CREATE FUNCTION getNameIdGeneration(idPar int, generationPar int)
  RETURNS VARCHAR(1000) READS SQL DATA
BEGIN
  DECLARE auxLoopVar INT default 0;
  DECLARE parentIdVar INT default idPar;  
  DECLARE nameVar VARCHAR(1000) default NULL;

  count_loop: LOOP    
    SELECT parent_id INTO parentIdVar FROM Category_parent WHERE Category_id = parentIdVar;
    SET auxLoopVar = auxLoopVar + 1;        
    IF auxLoopVar >= generationPar THEN
      LEAVE count_loop;
    END IF;    
  END LOOP;
  SELECT name INTO nameVar FROM Category WHERE id = parentIdVar;

  RETURN nameVar;
END;
$$
DELIMITER ;

assuming that Category_Id for CAT10 = 10 if you test the function given

select getNameIdGeneration(10, 2);

CAT3

Now all you need is a table which contains the id of the CAT you want to know its lineage

MariaDB [mydatabase]> select * from test;
+-------------+------------+
| category_id | generation |
+-------------+------------+
|          10 |          1 |
|          10 |          2 |
|          10 |          3 |
+-------------+------------+

MariaDB [mydatabase]> select generation, getNameIdGeneration(category_id, generation) as Name from test;
+------------+------+
| generation | Name |
+------------+------+
|          1 | CAT5 |
|          2 | CAT3 |
|          3 | CAT1 |
+------------+------+
3 rows in set (0.00 sec)

MySQL find all parents recursively

1 Answers1

Linked