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

  • Create a Pie Chart from single row, multiple column dataset in SSRS
  • Stored procedure setting a value if row is empty
  • SQL- Number of times each item is ordered each day
  • Cant read .bak files
  • Mapping CLR Parameter Data
  • How can I avoid encoding when using “FOR XML PATH”?
  • tbl_product:

    PID | productname
    1   | product 1
    2   | product 2
    3   | product 3
    4   | product 4
    ..
    

    tbl_categories, 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.

    https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.