How to get all parent id

Question

I have a MySQL table contains a list of categories.

    
    category_id         name                parent_id
    --------------------------------------------------
    1                    beverage           NULL
    2                    Water              1
    3                    Sparkling Water    2
    4                    Snacks             NULL
    5                    Chips              4

    
    product_id      name            category_id
    -------------------------------------
    1               water001        3   
    2               Chips001        5

How can I get the category path by one query?

SELECT name as product_name,category_path FROM product_table

    
    product_name    category_path
    -------------------------------------
    water001        3,2,1
    Chips001        5,4

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Nick, Jan 07 '19 at 07:51

score -1 · Answer 1 · answered Jan 07 '19 at 07:47

I think you need a stored procedure. I don't think you can do that with a single SQL-Statement.

I am not familiar to write SQL Procedures, but somtehintg like that can perhaps help:

CREATE FUNCTION GetPath(startingID INT)
@outputVal VARCHAR(20) OUTPUT 

  BEGIN
  DECLARE x INT;

  SELECT category_id INTO x FROM table2 Where product_id = startingID;
  SET outputVal = x;

  sloop:LOOP
      SELECT category_id INTO x FROM table1 WHERE parent_id = x;
      IF x IS NOT NULL THEN
          SET outputVal = outputVal + "," + x;
          ITERATE sloop;
      ELSE
          LEAVE sloop;
      END IF;  
  END LOOP;
  RETURN outputVal;
END $$

Please check the correct spelling!!!

To get the results you need something like:

SELECT GetPath(123);

But be careful: This solution can end with an not ending loop, when the procedure never finds a "NULL"!

Hope I Helped!

I think you can do it with a single sql statement (assuming the OP is using a version which supports CTE) — Strawberry, Jan 07 '19 at 11:27

How to get all parent id

1 Answers1