CODEWITHSUNDEEP
mysqlsqlleft-joincategories
kaluka19
kaluka19

Reputation: 33

categories and sub-categories sql order sorting

I have the following tables

table_name: categories 

id   name        parent  
16   Rash Vest   41  
17   Shorts      41  
20   Tops        41  
41   Shop        0  
47   Mens        16  
48   Womens      16

table_name: items 

id   title                 alias                 catid  
70   Rash Vest Black       rash-vest-black       47   
96   Rash Vest Red         rash-vest-red         47  
98   Rash Vest Womens Red  rash-vest-womens-red  48

Now I want to display all items according to the root tree..

Like This
Main Menu: SHOP
Sub Menus: Rash Vest | Shorts | Tops
Child categories:
Mens
----->Rash Vest Black
----->Rash Vest Red
Womens
----->Rash Vest Womens Red

Upvotes: 1

Views: 1181

Answers (1)

Fabio
Fabio

Reputation: 32445

SQL-SERVER versio:

;WITH groups AS
(SELECT ID
 , Name
 , ParentID
 , 0 AS Level
 , CAST(Name AS VARCHAR(255)) AS Path
 FROM Category WHERE ParentID = 0

 UNION ALL

 SELECT c.ID
 , c.Name
 , c.ParentID
 , g.Level + 1
 , CAST(CAST(g.Path AS VARCHAR(255)) + 
        CAST(' -> ' AS VARCHAR(255)) + 
        CAST(c.Name AS VARCHAR(255)) AS VARCHAR(255))
 FROM Category c 
 INNER JOIN groups g ON g.ID = c.ParentID
)

SELECT g.Name
, g.Level
, g.Path
, ISNULL(itm.Title, 'No items') AS Title
FROM groups g
LEFT JOIN Item itm ON itm.CategoryID = g.ID
ORDER BY g.Path

If you want show result as in question, than this part of work for User-interface(your application)

SQL Fiddle for example/testing

Upvotes: 1

Related Questions