I am a bit confused trying to use a recursive CTE to list all the items in a product catalogue for all the categories and their respective parent categories that each item belongs to.
The tables are really simple...
Category; Columns: Id, ParentID, TitleitemCategory; Columns: ItemID, CategoryIDI am just struggling to work out how to get the results I want. My best attempt is not right:
WITH
CTE (itemID, categoryID, title) AS (
SELECT itemID, categoryID, title
FROM itemcategory
INNER JOIN category ON category.ID = itemcategory.categoryID
UNION ALL
SELECT iI.ItemID, iI.categoryID, i.title
FROM itemcategory iI INNER JOIN category i ON i.ID = iI.categoryID
INNER JOIN CTE ON CTE.categoryID = i.ParentID)
SELECT * FROM CTE
I have a similar query which counts the number of items under each category:
WITH cte_count_category(id, parentid, c)
AS (SELECT c1.id,
c1.parentid,
(SELECT Count(*)
FROM (SELECT DISTINCT itemid
FROM itemcategory AS iI
WHERE iI.categoryid = c1.id) AS t1) AS c
FROM category AS c1
UNION ALL
SELECT c2.id,
c2.parentid,
d.c
FROM category c2
INNER JOIN cte_count_category d
ON c2.id = d.parentid)
SELECT cte_count_category.id,
cte_count_category.parentid,
title,
Sum(c) itemCount
FROM cte_count_category
LEFT JOIN category
ON category.id = cte_count_category.id
GROUP BY cte_count_category.id,
cte_count_category.parentid,
title
HAVING Sum(c) > 0
ORDER BY itemcount DESC;
I just can't work out how to get it to list all the items. Any help would be appreciated.
Edit: The categories need to be n deep although it is unlikely that it will go beyond 4 levels most of the time.
The output I am looking for is one row per itemID per category it falls under so, an item in the 'music book' category would appear under both the 'music book' category and the 'book' category.
I think the logic you need is confused by the items. You don't actually need them in the CTE (you can join them in afterwards). Alternatively, you can put them in the "base" part of the construct. Only the categories are needed in the recursive part.
This may be what you are looking for:
WITH CTE (itemID, categoryID, title, parentid, lev) AS (
SELECT ic.itemID, c.categoryID, c.title, c.parentid, 0
FROM itemcategory ic INNER JOIN
category c
ON c.ID = ic.categoryID
UNION ALL
SELECT cte.ItemID, c.categoryID, c.title, c.parentid, cte.lev + 1
FROM CTE INNER JOIN
category c
ON CTE.ParentID = c.categoryID
)
SELECT * FROM CTE
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments