CODEWITHSUNDEEP
sql-serverperformancet-sqlparent-child
V.B
V.B

Reputation: 1211

Parent-Children T-SQL Query

I have following DB schema

Location Table

LocationID   LocationName
1         |   L1
2         |   L2
.         |   L3
.         |   L4
.         |   L5  
n         |   Ln

and another table that defines the parent child relationship and the schema is

ID    LocationID    ParentID
1       1            null
2       2            1
3       3            1
4       4            2

and so on.

I want to pass in the ID or multiple IDs of the elements at any level and the query returns all the children of the element(s).

Upvotes: 0

Views: 1228

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @relation table(ID int, LocationID int, ParentID int)
insert @relation values
(1,1,null),(2,2,1),(3,3,1),(4,4,2)

declare @Location table (LocationID int,    LocationName varchar(3))
insert @Location values
(1,'L1'),(2,'L2'),(3,'L3'),(4,'L4'),(5,'L5'),(6,'L6')

;with a as
(
select ID p, ID, LocationID, ParentID from @relation where parentid is null
union all
select a.p, t.ID, t.LocationID, t.ParentID
from a join @relation t
on a.ID = t.ParentID
)
select L1.LocationID ParentID, L1.LocationName ParentName, L2.LocationID, L2.LocationName  from a
join @Location L1
on a.p = L1.LocationID
join @Location L2
on a.id = L2.LocationID
option (maxrecursion 500)

Result:

ParentID  ParentName LocationID LocationName
1         L1         1          L1
1         L1         2          L2
1         L1         3          L3
1         L1         4          L4

Upvotes: 1

Chamal
Chamal

Reputation: 1449

select l.LocationID,l.LocationName,m.LocationID AS Child,l1.LocationName AS ChildName
from Location l
inner join mappingtable m
ON l.LocationID=m.ParentID
inner join Location l1
ON l1.LocationID=m.LocationID

SQL FIDDLE DEMO

Upvotes: 1

Related Questions