如果您询问"mysql递归查询中是否存在?"回答否".

但是有很好的处理方法.

创建助手表(说CatHierarchy)

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

此冗余数据可以轻松地在1个查询中选择任何层次结构，并在2个插入中支持任何层次结构(在1个查询中还通过删除级联完整性来执行删除操作).

那是什么意思.您跟踪层次结构中的所有路径. Cat的每个节点必须添加对自身的引用(距离0)，然后通过添加有关链接的节点的冗余数据来支持复制.

要选择带有子类别，只需输入:

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

someSpecifiedRootOfCat-是用于指定类别根目录的参数 就是这样！

I would like to know if it's possible to extract the categories and sub-categories in a single DB fetch.

My DB table is something similar to that shown 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 as 3 AND the row 3 itself AND all the parents of row 3 should be fetched.

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 a single DB fetch or multiple? Or are there any other better methods?

Thanks!!!

If you asking about "Is there in mysql recursive queries?" answer "NO".

But there is very good approach to handle it.

Create helper table (saying CatHierarchy)

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

This redundant data allows easily in 1 query to select any hierarchy, and in 2 insert support any hierarchy (deletion also performed in 1 query with help of delete cascade integrity).

So what does this mean. You track all path in hierarchy. Each node of Cat must add reference to itself (distance 0), then support duplication by adding redundant data about nodes are linked.

To select category with sub just write:

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

someSpecifiedRootOfCat - is parameter to specify root of category THATS ALL!