CODEWITHSUNDEEP
mysqlsql
Manoj Rana
Manoj Rana

Reputation: 3440

sql query to get all sub-categories and their sub-categories and so on of a category

my categories table is 

id          parent_id
----------   -----------
1           0
2           1
3           1
4           2
5           3
6           2
7           4
8           3
9           5
10          6
11          7
12          4
13          10
14          9

suppose I want to get categories of category_id 2 then output should be like as

id 2,4,6,7,10,11,12,13

and for category 3 o/p should be 

id 3,5,8,9,14

and for 1 should be 

id 1,2,3,4,5,6,7,8,9,10,11,12,13,14

and for 4 should be 

id 4,7,11,12

please help to get this query

Upvotes: 0

Views: 1319

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Perhaps it should be something like that;

For SQL Server

WITH PR AS 
(
    SELECT * , id AS parent
    FROM table c
    UNION ALL
    SELECT c.* , r.parent
    FROM table c
    INNER JOIN PR r ON c.parent_id = r.id
)
SELECT 
  r.parent AS parent_id
, r.id AS Childid 
FROM PR r
ORDER BY parent_id;

Upvotes: 1

Manoj Rana
Manoj Rana

Reputation: 3440

after a lot of searched, I got this solution and it's working for me if anyone has a better solution then please share and you also can comment drawback of this below query on this answer

select id from (select * from categories order by parent_id, id) categories, (select @pv := '2') initialisation where find_in_set(parent_id, @pv) > 0 and @pv := concat(@pv, ',', id)

Upvotes: 2

Related Questions