求一个存储过程

[苦逼de小Coder]

有这么两张表

例如要求查询id为XX下的所有子分类id与其子分类下所有信息
求帮助

[--小F--]

;with f as 
(
select * from tb where id=xx
union all
select a.* from tb as a inner join f as b on a.parentid=b.id
)

select * from f

[苦逼de小Coder]

引用 1 楼 fredrickhu 的回复:

;with f as 
(
select * from tb where id=xx
union all
select a.* from tb as a inner join f as b on a.parentid=b.id
)

select * from f

额。。关联的第二张表的count字段内容没有啊

[苦逼de小Coder]

可能我没说明白，第一张表的id 就是 第二张表的cid

[--小F--]

;with f as 
(
select * from tb1 where id=xx
union all
select a.* from tb1 as a inner join f as b on a.parentid=b.id
)
 
select * from f  as a inner join tb2 as b on a.id=b.cid

[MootShao]

思路：使用CTE的递归 求树的上级或者下级

[苦逼de小Coder]

引用 4 楼 fredrickhu 的回复:

;with f as 
(
select * from tb1 where id=xx
union all
select a.* from tb1 as a inner join f as b on a.parentid=b.id
)
 
select * from f  as a inner join tb2 as b on a.id=b.cid

不好意思，前几天有事了，所以没有及时结贴。非常感谢