Selecting hierarchy from a self referencing table

Question

 

I have the following SP which returns a single row based on the ID passed (int identity field)

SELECT ManufacturerID, ManufacturerName, ManufacturerDescription, ManufacturerURL, ParentManufacturerID, LastUpdated
FROM Manufacturers
WHERE (M.ManufacturerID = @ManufacturerID)

Can anyone recommend the best way to modify this to select not only the matching ID row but also any rows which have matching ParentManufacturerID's,  and those rows parents etc... ie recursively all the way till the selected parent rows hit the parent's column default ID of -1?

Thanks
Mac

 

Answer 1

 

try this

declare @Parent table(ID int)
insert into @Parent values( @ManufacturerID)

declare @ParentExists int
select @ParentExists=count(ParentManufacturerID) from Manufacturers where ManufacturerID in (select ID from @Parent)and  ParentManufacturerID not in (select ID from @Parent) and ParentManufacturerID<>-1
while @ParentExists>0
begin

insert into @Parent select ParentManufacturerID from Manufacturers where ManufacturerID in (select ID from @Parent)and  ParentManufacturerID not in (select ID from @Parent)and ParentManufacturerID<>-1
select @ParentExists=count(ParentManufacturerID) from Manufacturers where ManufacturerID in (select ID from @Parent)and  ParentManufacturerID not in (select ID from @Parent)and ParentManufacturerID<>-1

end

SELECT ManufacturerID, ManufacturerName, ManufacturerDescription, ManufacturerURL, ParentManufacturerID, LastUpdated
FROM Manufacturers where ManufacturerID in (select id from @Parent)

 

regards

 

Answer 2

Please mention the version of SQL Server you are using. You can use recursive CTEs in SQL Server 2005. See the BOL topic below:

 

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

 

Answer 3

 

Thank you both for your help, it is version 2005 so I had a look at CTE's as you suggested and it's working a treat now

WITH ManufacturerAndParents
AS
(
--Anchor member definition
SELECT ManufacturerID,
ManufacturerName,
ManufacturerDescription,
ManufacturerURL,
ParentManufacturerID,
LastUpdated
FROM Manufacturers
WHERE ManufacturerID = @ManufacturerID
UNION ALL

-- Recursive member definition
SELECT m.ManufacturerID,
m.ManufacturerName,
m.ManufacturerDescription,
m.ManufacturerURL,
m.ParentManufacturerID,
m.LastUpdated
FROM Manufacturers AS m
INNER JOIN ManufacturerAndParents AS mp ON m.ManufacturerID = mp.ParentManufacturerID
)

-- Statement to execute the common table expression (CTE)
SELECT ManufacturerID, ManufacturerName, ManufacturerDescription, ManufacturerURL, ParentManufacturerID, LastUpdated
FROM ManufacturerAndParents;