GSquared (4/18/2008)
MikeAngelastro (3/13/2008)
I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?
A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.
GSquared,
Thanks for your input.
I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none.
Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:
SELECT DISTINCT FROM CTE
where “CTE” is the CTE’s record set result - extra rows and all.
And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?
Thanks,
Mike