recursive query

[cathiec]

Hi all,

I have the following query


(SELECT
a.strPlantAreaName, a.intPlantAreaID

FROM dbo.tblSafeTrackIT_Config_Area a
WHERE a.intParentPlantAreaID = 126 or a.intPlantAreaID = 126
UNION ALL


SELECT
b.strPlantAreaName, b.intParentPlantAreaID

FROM dbo.tblSafeTrackIT_Config_area a left JOIN tblSafeTrackit_Config_Area b ON a.intPlantAreaID = b.intPlantAreaID
WHERE b.intPlantAreaID = a.intParentPlantAreaID and a.bolIsMaster <>1 )

This selects all child plant areas that have the parent id 126 and it works fine.

But in some cases the child plant areas will also have children themseleves who may also have children. An area has a subarea and this subarea may have a subarea and so on.

What i cant get the query to do is to check if the child plant area has a child itself and if it does then use the plantareaid of the child to pull back records that have the parentplantareaid set to this.

sorry about the confusion but basically i want to loop through child areas plantareas checking to see if any other nodes have that id as their parentplantareaid

Thanks!
Cathie

 

[r937]

SQL Server, right? you might want to ask in the SQL Server forum

r937.com | rudy.ca

 

[r937]

specifically, this SQL Server faq: faq183-5322

r937.com | rudy.ca