WITH a AS ( SELECT * FROM mbrDataSrc_F WHERE [ID] = 'Top_Level_Datasource_Name' --top parent UNION ALL SELECT b.* FROM mbrDataSrc_F b JOIN a ON b.PARENTH1 = a.[ID] ) SELECT * FROM tblFactForecast WHERE DataSrc_F IN ( SELECT [ID] FROM a )If you're not familiar with them, the SQL after the UNION is the recursive part (some more info on recursive Common Table Expressions). I use them a lot in place of derived tables for their slightly cleaner syntax, but don't usually have too much of a need for recursion.
BTW, I don't believe in SELECT *, except for ad-hoc queries, which this one was for me. There is an interesting discussion about it over at StackOverflow.com - basically, minimal performance gains in most situations, but more clean and safer to select the actual columns.