punatiainen punatiainen - 6 months ago 12
SQL Question

How to find a full node in a recursive SQL query

I would like to get the full nodes from existing recursive category tree, if the products table has a matching category.

I have the following.

WITH ret AS (
SELECT ID, ParentID, ProductCategoryId, ProductCategoryName
FROM ProductCategoryTree as p1, Products as p2
WHERE p1.ProductCategoryId = p2.ProductCategoryId
UNION ALL
SELECT p.ID, p.ParentID, p.ProductCategoryId, p.ProductCategoryName
FROM ProductCategoryTree as p INNER JOIN
ret r ON p.ParentID = r.ID
)
SELECT DISTINCT ID, ParentID, ProductCategoryId, ProductCategoryName
FROM ret;


This will give me the category of the product and all the child nodes of that category, but I would also need to get the category (or categories) from the upper level(s).

Any help would be much appreciated!

Answer

As we discussed in the comments, you just reverse the parent-child relation on the last row of the join, and it will work:

ret r ON p.ParentID = r.ID