CTE Recursive

Question

Post reply

CTE Recursive

hoseam

SSCertifiable

Points: 5533
More actions
November 12, 2013 at 3:57 am

#282443

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')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul Keys SSC Eights! Points: 866 More actions · Answer 1

Hi,

Try changing your clause for the inner join to

ON loc.Parent_Location = loh.LocationID

hoseam SSCertifiable Points: 5533 More actions · Answer 2

hoseam

SSCertifiable

Points: 5533

November 12, 2013 at 4:11 am

#1665688

Thank you.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Paul Keys (11/12/2013)
Hi,
Try changing your clause for the inner join to
ON loc.Parent_Location = loh.LocationID

With this change, he can loose the WHERE clause 🙂

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2