How to get a hierarchical category products from top to down in sql

Question

I keep product categories with parentid logic. I need to get all products under a category, but from top to down hierarchy. For example I have these tables:

ProductCategories

id   CategoryID   ProductID
1    4            1
2    5            2
3    6            3
4    8            4
5    8            5
6    9            5
7    9            2

Categories

ID     CategoryName    ParentID
1      Kids            NULL
2      Accesories      1
3      Shoes           2
4      Flat Shoes      3
5      Leather Sandals 4
6      Sneakers        3
7      Clothing        1
8      T-Shirts        7
9      Bestsellers     1

Products

ID   ProductName
1    White Espadrilles
2    Tan Leather Sandals
3    Beige Sneakers
4    Linen T-Shirt
5    Cotton T-Shirt

I use below Sql recursive query:

with CTE as
(
select c.ID,c.ParentID,c.CategoryName,p.ProductName 
from Categories c
join ProductCategories pc
  on pc.CategoryID=c.ID
join Products p
  on pc.ProductID=p.ID
where c.ID = 5 --start id

union all
select ce.ID,ce.ParentID,ce.CategoryName,p.ProductName 
from Categories ce
join ProductCategories pc
  on pc.CategoryID=ce.ID
join Products p
  on pc.ProductID=p.ID
join CTE
  on ce.ID=CTE.ParentID 
)
select * from CTE

Above query returns below result for a given CategoryID = 5 :

ID     ParentID     CategoryName      ProductName
5      4            Leather Sandals   Tan Leather Sandals
4      3            Flat Shoes        White Espadrilles

if categoryID = 1 or 2 no record comes.

I dont have a product directly for Accesories category, but I have for it's child categories, so I should get all products under that, from top to down.

How can I do this?

First explore only categories with recursive CTE and then join to Products. othewise missing products in a category breaks the recursion. — Serg, May 17 '19 at 21:14
`White Espadrilles` is wrong, since is not part of category `5`. Is it? — The Impaler, May 17 '19 at 21:17
@TheImpaler White Espadrilles categoryID is 4. I use Microsoft Sql Database — Zeynep, May 17 '19 at 21:38

score 3 · Accepted Answer · answered May 17 '19 at 21:21

You don't say which database you are using so I will give you a generic solution.

You can use a recursive CTE to produce a list of categories that include the starting one, plus all its children (in multiple levels). Then simple joins will do the rest, as you have already tried.

Here's an example. Tweak as necessary for your specific database:

with
categories_subtree as (
  select id, categoryname, parentid
  from categories 
  where id = 5 -- starting category
  union all
  select c.id, c.categoryname, c.parentid
  from categories_subtree s
  join categories c on c.parentid = s.id
)
select
  p.id,
  c.parentid
  c.categoryname,
  p.productname
from categories_subtree c
join productcategories pc on pc.categoryid = c.id
join products p on p.id = pc.productid

Thank you! Is there a way to use `left outer join` in your solution? or should I find another way to do that? — Zeynep, May 17 '19 at 22:41

How to get a hierarchical category products from top to down in sql

1 Answers1