Using Recursive Query Using CTE

Question

I'm trying to write an efficient recursive query. I've run across CTEs and have found a number of examples. However, every example I've found is returning the same data that is recursive where I need to return some different data.

For example, my hierarchical data is a table of Locations.

[Locations]
ID int
Title nvarchar(100)
ParentLocationID int

But the data I want to return is in a table of Activities. I want to return all activities associated with a particular location, and all "child" locations.

[Activities]
ID int
Title nvarchar(100)
LocationID int

I'm new to CTEs and can't quite see how to do this from the examples I have. Any help would be appreciated.

Martin Smith · Accepted Answer · 2011-12-05 23:09:43Z

4

Use the recursive CTE to find all locations in the hierarchy then join the Activities table onto the result of that.

;WITH R
     AS (SELECT ID,
                Title,
                ParentLocationID
         FROM   [Locations]
         WHERE  ID = @LocationId
         UNION ALL
         SELECT L.ID,
                L.Title,
                L.ParentLocationID
         FROM   [Locations] L
                JOIN R
                  ON L.ParentLocationID = R.ID)
SELECT * /*TODO: Select columns of interest*/
FROM   R
       JOIN [Activities] A
         ON A.LocationID = R.ID

answered Dec 5, 2011 at 23:09

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Wood Over a year ago

I see now. You are just doing what the examples I've seen were doing, and then selecting related rows by doing a join on the results. Thanks!

Collectives™ on Stack Overflow

Using Recursive Query Using CTE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related