show only categories that have products in them
excuse the bad title but I couldn’t find a good way to express what I want in abstract terms.
Anyway I have 3 tables
PID | productname 1 | product 1 2 | product 2 3 | product 3 4 | product 4 ..
motherCategory allows me to nest categories:
CID | categoriename | motherCategory 1 | electronics | NULL 2 | clothing | NULL 3 | Arduino | 1 4 | Casings, extra's | 3 ..
tbl_productInCategory PID and CID are foreign keys to PID and CID in tbl_product and tbl_categories respectively. A product can have multiple categories assigned to it so PID can occur more than once in this table.
PID | CID 1 | 1 2 | 1 3 | 3 4 | 4
Now I have a query that returns all categories if I give the mothercategory.
What I want to do is show ONLY the categories that have products in them recursively.
for instance on the example data above I show all categories(motherCategory is null), I want it to return only electronics since there are no products category 2, clothing.
However the problem I am having is that I also want this to work recursively. Consider this tbl_productInCategory:
PID | CID 1 | 2 2 | 2 3 | 2 4 | 4
Now it should return both clothing and electronics even though there are no products in electronics, because there are products in the nested category arduino->Casings, extra’s. If I show all categories with motherCategory, electronics it should also return arduino.
I can’t figure out how to do this and any help or pointers are appreciated.
3 Solutions collect form web for “show only categories that have products in them”
First you should select all categories where products exist. On the next steps select mother categories.
WITH CTE AS ( SELECT tbl_categories.* FROM tbl_categories JOIN tbl_productInCategory on tbl_productInCategory.CID = tbl_categories.CID UNION ALL SELECT tbl_categories.* FROM tbl_categories JOIN CTE on tbl_categories.CID = CTE.motherCategory ) SELECT DISTINCT * FROM CTE
Use a recursive CTE to get a derived table of your category tree, and then INNER JOIN it to your ProductCategory table.
It’s not something I’ve done before, but some googling indicates it is possible.
The semantics of the recursive execution is as follows:
Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.
USE AdventureWorks2008R2; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS ( -- Anchor member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL WHERE ManagerID IS NULL UNION ALL -- Recursive member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1 FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) -- Statement that executes the CTE SELECT ManagerID, EmployeeID, Title, DeptID, Level FROM DirectReports INNER JOIN HumanResources.Department AS dp ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0; GO