Trying to replace a Cross Apply with a JOIN

We have a table, ProductHierarchy, which is set. The # of rows will not change. It has simple parent/child Product Hierarchy data in it.

We also have a Table-valued function which takes a ProductHierarchyId and then returns all rows where IsDescendantOf is true for that Id. Or in other words, it returns that row, plus all of its ancestors.

The problem is that with this Table-valued function, we have to use it with CROSS APPLY, and this is seriously slowing down the query.

My thought is to create either a second permanent table (or a temp table/table variable in the query in question) that has all possible results from the Table-valued function already in it. And then JOIN to that table instead of using the CROSS APPLY. In this table, we would add a column called something like QueryID. So instead of

CROSS APPLY dbo.GetProductLevels(P.ProductHierarchyId)

We could use

LEFT JOIN FutureTable ft ON ft.QueryId = P.ProductHierarchyId

I’m struggling with the query to create that table. Here’s what I have so far…

SELECT
    *
    , 1 AS QueryId
FROM 
    dbo.ProductHierarchy
WHERE 
    (SELECT ProductHierarchyNode FROM dbo.ProductHierarchy WHERE ProductHierarchyId = 1).IsDescendantOf(ProductHierarchyNode) = 1 

Ok, so that works great for the record where ProductHierarchyId = 1. But then I’d need to repeat that for ProductHierarchyId = 2:

SELECT
    *
    , 2 AS QueryId
FROM 
    dbo.ProductHierarchy
WHERE 
    (SELECT ProductHierarchyNode FROM dbo.ProductHierarchy WHERE ProductHierarchyId = 2).IsDescendantOf(ProductHierarchyNode) = 1 

And then for 3, and then for 4, all the way to the last Id, doing a UNION each time, inside a loop — which is hideous.

I KNOW there is a way to do this all in one query. Something like a recursive CTE. But my brain isn’t getting there.

Answer

Wouldn’t you just do this?

SELECT . . .    -- the columns you want
INTO . . .      -- where you want them
FROM dbo.ProductHierarchy ph CROSS APPLY
     dbo.GetProductLevels(P.ProductHierarchyId);