CODEWITHSUNDEEP
sqlsql-server
Daniel Minnaar
Daniel Minnaar

Reputation: 6295

Returning all nodes in the tree in SQL

I have a table which lists companies, and a table that links these companies together to form an ownership hierarchy:

dbo.Suppliers:

dbo.SupplierLinks:

I'd like to create a stored procedure (or function?), that accepts a supplier Id, and returns rows of all linked (either directly or indirectly) suppliers.

Of course, this doesn't return indirectly linked suppliers:

DECLARE @SupplierId int
SET @SupplierId = 5

SELECT * 
FROM Config.SupplierLinks sl
WHERE sl.ParentId = @SupplierId
OR sl.SupplierId = @SupplierId

How can I accomplish this?

Example:

Supplier 1
   |
  / \
2    3
     |
    /\
   5  4

Data:

Id      ParentId        Name

4       3               'Supplier 4'
5       3               'Supplier 5'
3       1               'Supplier 3'
2       1               'Supplier 2'

*Note: Supplier 1, the top-level parent, does not exist in the link table above.

Upvotes: 0

Views: 143

Answers (1)

huMpty duMpty
huMpty duMpty

Reputation: 14470

Have a look at Recursive Queries Using Common Table Expressions

;WITH Parents
AS
(
    Select SP1.SupplierId , SP1.ParentId from SupplierLinks SP1 where SP1.SupplierId = @SupplierId
    Union All
    Select SP2.SupplierId , SP2.ParentId
    From SupplierLinks SP2 Inner Join Parents C on C.ParentId = SP2.SupplierId
)

Decalare @ParentId int
Select @ParentId = P.SupplierId
From Parents as P
Where parentid = 0 or ParentId is null

;WITH Children
AS
(
    Select SP1 .SupplierId , SP1 .ParentId from SupplierLinks SP1 where SupplierId = @ParentId
    Union All
    Select SP2.SupplierId , SP2.ParentId
    From SupplierLinks SP2 Inner Join Children C on C.SupplierId = SP2.parentid
)

Upvotes: 1

Related Questions