SQL Query of 2 Tables forming a category tree

0
votes

I have 2 Tables. The first one is the Categorie's IDs.

Category

CategoryID ParentID
1 0
2 1
3 1
4 2
5 3
6 5

Category Name

CategoryID Name
1 Top
2 Cat1
3 Cat2
4 Cat3
5 Another1
6 RandCat

Need to produce a query that will return the Category Tree.

CategoryID Cat Name Parent Cat Name
1 Top
2 Cat1 Top
3 Cat2 Top
4 Cat3 Cat1
5 Another1 Cat2
6 RandCat Another1

We can have up to 3 layers of SubCategories and would have to grow the table as required.

Thanks For any help!

2
mysqlsqldatabase-design

2 Answers

0
votes

You need left join with base table as category_name.

Select cn.categoryid, cn.cat_name,
       Cnp.cat_name as parent_category
  From category_name cn
  Left join category c on c.categoryid = cn.categoryid
  Left join category_name cnp on cnp.categoryid = c.parentid;
0
votes

This looks like JOINs:

select c.categoryid, cn.name, cnp.name
from category c left join
     categoryname cn
     on cn.categoryid = c.categoryid left join
     categoryname cnp
     on cnp.categoryid = c.parentid;