Now I see where you're at. It wasn't obvious to me from looking at your T-SQL that the potential for a heirarchical relationship beyond option and product beyond one level was involved. Perhaps a recursive CTE is in order then? I read about that recently and the concept was tantalizing, although I can't say I fully understood exactly how it achieves it's goal. Similarly to your product with an option that can be a product in it's own right, imagine the heirarchy of employees and their reporting relationships (Joe reports to Henry, who reports to Jeff, etc...). Assuming that all the employees are in one table, such as is the case with all your products and options, I would expect the following example might be applicable (it uses Northwind's employee table):

--Start with a covering index that will allow fetching direct subordinates

-- of each manager using a single seek plus a partial scan

CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname

ON dbo.Employees(ReportsTo, EmployeeID)

INCLUDE(FirstName, LastName);

--Here's the recursive CTE, which identifies a specific employee whose

-- entire organization is the result set.

DECLARE @Employee int

SET @Employee = 5;

With EmpsCTE AS (

SELECT EmployeeID, ReportsTo, FirstName, LastName

FROM dbo.Employees

WHERE EmployeeID = @Employee

UNION ALL

SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName,

EMP.LastName

FROM EmpsCTE AS MGR

JOIN dbo.Employees AS EMP

ON EMP.ReportsTo = MGR.EmployeeID

)

SELECT * FROM EmpsCTE;

The thing you have to watch out for is infinite recursion, which could occur if you ever had a product that could have itself as an option. However, you can specify a maximum recursion level as an option in the outer query to prevent recursion beyond some number of levels, which is 100 by default. Removing that limit entirely requires setting it to zero.

If I understand it correctly, the reference to the CTE within itself just means "the previous result set", and an empty result set stops the recursion at that level.

As useful as this method is, employees must of necessity ultimately report up to a single entity, whereas with products and options, each product is independent. Perhaps that would just mean that to apply this to your case, your anchor query (the one before UNION ALL) would have to be ALL your products? Of course, if not every record in your table represents a product and there are some records that only represent options, there would need to be a field in the record that could indicate that, and the anchor query would need to exclude those records.

Does that make sense?

Steve

(aka smunson)

:):):)