How to get categories and subcategories in one query in sql? (MySQL)

Question

I would like to know if categories and subcategories can be retrieved in a single DB sample.

My DB table is like below

Table

cat_id parent_id
1      0
2      1
3      2
4      3
5      3
6      1

i.e. when the input is 3, then all the rows with parent_id are 3 And the row itself 3 And all the parents of row 3 should be extracted.

Output

cat_id parent_id
3      2   -> The row 3 itself
4      3   -> Row with parent as 3
5      3   -> Row with parent as 3
2      1   -> 2 is the parent of row 3
1      0   -> 1 is the parent of row 2

Can this be done using stored procedures and loops? If so, will it be one database sample or several? Or are there other best practices?

Thank!!!

Answer 1

If you ask about "Are there recursive queries in mysql?" answer "NO".

But there is a very good approach to handling it.

( CatHierarchy)

CatHierarchy:
    SuperId, ChildId, Distance
------------------------------ 
     1          1         0
     1          2         1
     2          2         0

1 , 2 ( 1 ).

, . . node Cat ( 0), .

- :

 SELECT c.* from Category c inner join CatHierarchy ch ON ch.ChildId=c.cat_id
      WHERE ch.SuperId = :someSpecifiedRootOfCat

someSpecifiedRootOfCat - , !

Answer 2

Theres Sitepoint -

Answer 3

It's complicated. I assume that you want to display categories, like the type of folder? Three fields: MainID, ParentID, Name ... Applies to your table, and it should work like a charm. I think it is called a recursive query?

WITH CATEGORYVIEW (catid, parentid, categoryname) AS
(
SELECT catid, ParentID, cast(categoryname as varchar(255))
  FROM [CATEGORIES]
 WHERE isnull(ParentID,0) = 0

UNION ALL

SELECT C.catid, C.ParentID, cast(CATEGORYVIEW.categoryname+'/'+C.categoryname as varchar(255))
  FROM [CATEGORIES] C
  JOIN CATEGORYVIEW ON CATEGORYVIEW.catID = C.ParentID
)
SELECT * FROM CATEGORYVIEW ORDER BY CATEGORYNAME