[SQL] T-SQL recursive query (a method for a given node to check all parent nodes, all child nodes)

Source: Internet
Author: User


--Find all parent nodes
With TAB as
(
Select Type_id,parentid,type_name from sys_paramtype_v2_0 where type_id=316--child nodes
UNION ALL
Select B.type_id,b.parentid,b.type_name
From
tab a,--child node datasets
Sys_paramtype_v2_0 B--parent node data set
Where a.parentid=b.type_id--the child node DataSet. Parendid= The parent node data set. Id
)
SELECT * from tab;

--Find all child nodes
With TAB as
(
Select Type_id,parentid,type_name from sys_paramtype_v2_0 where type_id=1--parent node
UNION ALL
Select B.type_id,b.parentid,b.type_name
From
tab a,--parent node dataset
SYS_PARAMTYPE_V2_0 b--child node data set
Where b.parentid=a.type_id--the child node DataSet. Id= The parent node data set. Parendid
)
SELECT * from tab;

--Find the path from the child node to the tier node
With TAB as
(
Select Type_id,parentid,type_name,cast (type_id as varchar) as Fulltypeid
From Sku_producttype where type_id=423--child nodes
UNION ALL
Select
B.type_id,b.parentid,b.type_name,
Cast (a.fulltypeid+ ', ' +cast (b.type_id as nvarchar) as varchar) as Fulltypeid
From
tab a,--child node datasets
Sku_producttype B--parent node data set
Where a.parentid=b.type_id--the child node DataSet. Parendid= The parent node data set. Id
)
SELECT * from tab;

--------------Results--------------
423 410 Bee Pollen 423
410 347 Bee Products 423,410
347 5 Nutritious Food 423,410,347
5 0 Health Care 423,410,347,5

[SQL] T-SQL recursive query (a method for a given node to check all parent nodes, all child nodes)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.