SQL: Recursively get parent records using Common Table Expressions
- by Martijn B
Hi there,
Suposse you have to following tables where a sale consists of products and a product can be placed in multiple categories. Whereby categories have a hierachly structure like:
Man
Shoes
Sport
Casual
Watches
Women
Shoes
Sport
Casual
Watches
Tables:
Sale:
id name
1 Sale1
Product:
id saleidfk name
1 1 a
2 1 b
3 1 c
4 1 d
5 1 e
ProductCategory :
productid categoryid
1 3
2 3
3 4
4 5
5 10
Category:
id ParentCategoryIdFk name
1 null Men
2 1 Shoes
3 2 Sport
4 2 Casual
5 1 Watches
6 null Women
7 6 Shoes
8 7 Sport
9 7 Casual
10 6 Watches
Question:
Now on my website I want to create a control where only the categories are shown of a certain sale and where the categories are filled with the products of the sale. I also want
to include the hierachly structure of the categories. So if we have a leave node, recusivly go up to the top node.
So with sale1 I should have a query with the following result:
Men
Shoes
Sport
Casual
Watches
Women
Watches
This thing is driving me crazy :-)
Thanks in advance!
Gr
Martijn