CTE Recursive - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

CTE Recursive

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-11-12 : 05:57:23

I have this data and CTE recursion below. When I try to run it I get only one row back, not all the rows I'm expecting. please help.

WITH LOCATIONHIERARCHY(LocationID, Parent_location, Location_name, [Level])
AS (
-- Anchor member definition
SELECT LocationID, Parent_location, Location_name, 0 AS [Level]
FROM Location
WHERE Parent_location IS NULL

UNION ALL
-- Recursive member definition
SELECT loc.LocationID, loc.Parent_location, loc.Location_name, [Level] + 1
FROM Location loc
INNER JOIN LOCATIONHIERARCHY loh
ON loc.LocationID = loh.LocationID
WHERE loc.Parent_location IS NOT NULL
)

SELECT *
FROM LOCATIONHIERARCHY

insert into Location (LocationID, Parent_Location, Location_Name)
values(1000, null,'South Africa')

insert into location (LocationID, Parent_Location, Location_Name)
values(1001, 1000, 'Gauteng')

insert into location (LocationID, Parent_Location, Location_Name)
values(1002, 1000, 'Eastern Cape')

insert into location (LocationID, Parent_Location, Location_Name)
values(1003, 1000, 'Western Cape')

insert into location (LocationID, Parent_Location, Location_Name)
values(1004, 1000, 'Free State')

insert into location (LocationID, Parent_Location, Location_Name)
values(1005, 1000, 'North West')

insert into location (LocationID, Parent_Location, Location_Name)
values(1006, 1000, 'Northern Cape')

insert into location (LocationID, Parent_Location, Location_Name)
values(1007, 1000, 'Limpopo')

insert into location (LocationID, Parent_Location, Location_Name)
values(1008, 1000, 'Mpumalanga')

insert into location (LocationID, Parent_Location, Location_Name)
values(1009, 1000, 'KZN')

insert into location (LocationID, Parent_Location, Location_Name)
values(1011, 1001, 'Johannesburg')

insert into location (LocationID, Parent_Location, Location_Name)
values(1012, 1001, 'Pretoria')

insert into location (LocationID, Parent_Location, Location_Name)
values(1013, 1002, 'East London')

insert into location (LocationID, Parent_Location, Location_Name)
values(1014, 1003, 'Cape Town')

insert into location (LocationID, Parent_Location, Location_Name)
values(1015, 1005, 'Rustenburg')

insert into location (LocationID, Parent_Location, Location_Name)
values(1016, 1007, 'Polokwane')

insert into location (LocationID, Parent_Location, Location_Name)
values(1017, 1006, 'Kimberly')

insert into location (LocationID, Parent_Location, Location_Name)
values(1018, 1004, 'Bloemfontein')

insert into location (LocationID, Parent_Location, Location_Name)
values(1019, 1009, 'Durban')

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-12 : 06:39:05

[code]
;WITH LOCATIONHIERARCHY(LocationID, Parent_location, Location_name, [Level])
AS (
-- Anchor member definition
SELECT LocationID, Parent_location, Location_name, 0 AS [Level]
FROM Location
WHERE Parent_location IS NULL

UNION ALL
-- Recursive member definition
SELECT loc.LocationID, loc.Parent_location, loc.Location_name, [Level] + 1
FROM Location loc
INNER JOIN LOCATIONHIERARCHY loh
ON loc.Parent_location = loh.LocationID
WHERE loc.Parent_location IS NOT NULL
)

SELECT *
FROM LOCATIONHIERARCHY

[/code]

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-12 : 06:40:02

[code]WITH LOCATIONHIERARCHY(LocationID, Parent_location, Location_name, [Level])
AS (
-- Anchor member definition
SELECT LocationID, Parent_location, Location_name, 0 AS [Level]
FROM Location
WHERE Parent_location IS NULL

UNION ALL
-- Recursive member definition
SELECT loc.LocationID, loc.Parent_location, loc.Location_name, [Level] + 1
FROM Location loc
INNER JOIN LOCATIONHIERARCHY loh
ON loc.Parent_Location = loh.LocationID
WHERE loc.Parent_location IS NOT NULL
)

SELECT *
FROM LOCATIONHIERARCHY
[/code]

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-12 : 06:40:36

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources