how to display categories like ..... [Resolved]

Posted by Kashif under ASP.NET on 7/12/2013 | Points: 10 | Views : 1732 | Status : [Member] | Replies : 8
Hi

i have successfully solve my previous problems...nw i have to display categories like Parent>child>sub-child....e.g Mens>>clothing>>shirts
my category table is like categories(category_id,category_name,parent_id)

any idea>>??
rep anyone




Responses

Posted by: Bandi on: 8/1/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
-- check the below sample input & output
-- Input Data
DECLARE @Category TABLE(category_id int, category_name varchaR(10), parent_id int)
insert into @Category
SELECT 1, 'Mens', NULL union all
SELECT 2, 'Ladies', NULL union all
SELECT 3, 'Clothing', 1 union all
SELECT 4, 'Shirts', 3 union all
SELECT 5, 'Jewellery', 2 union all
SELECT 6, 'Rings', 5
-- Hierarchical query
;WITH RecCTE

AS (SELECT category_id AS ID, CAST( category_name AS VARCHAR(500)) AS Chaining, Parent_id
FROM @Category
WHERE parent_id IS NULL
UNION ALL
SELECT category_id , CAST( Chaining+'-->'+Category_name AS VARCHAR(500)), c.Parent_id
FROM RecCTE r
JOIN @Category c ON c.parent_id=r.Id
)
SELECT * FROM RecCTE


--Output
ID	Chaining	Parent_id

1 Mens NULL
2 Ladies NULL
5 Ladies-->Jewellery 2
6 Ladies-->Jewellery-->Rings 5
3 Mens-->Clothing 1
4 Mens-->Clothing-->Shirts 3


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/22/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
;WITH RecCTE

AS (SELECT category_id AS ID, CAST( category_name AS VARCHAR(500)) AS Chaining, Parent_id
FROM @Category
WHERE parent_id IS NULL
UNION ALL
SELECT category_id , CAST( Chaining+'-->'+Category_name AS VARCHAR(500)), c.Parent_id
FROM RecCTE r
JOIN @Category c ON c.parent_id=r.Id
)
SELECT * FROM RecCTE

the above is the Recursive query.. it will consider all records in the table......
You just replace your table name with the highlighted part.....
Mark the above post as answer if it helps

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/1/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can see the below link.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
http://sqllessons.com/categories.html

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kashif on: 8/22/2013 [Member] Starter | Points: 25

Up
0
Down
HI Bandi

i have got my solution in your answer.just tell me one thing.have you already created the table "RecCTE".
and when i add new category in my categories table how can it be refresh in RecCTE table.


Reply
kashif


Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Kashif,

Can you please mark it as answer if you satisfied with the solution

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kashif on: 9/1/2013 [Member] Starter | Points: 25

Up
0
Down
; WITH cte AS (
select CategoryId, CategoryName, ParentId,
cast(CategoryName as varchar(max)) as xpath
from
categories
where ParentId = 0
UNION ALL
select c.CategoryId, c.CategoryName, c.ParentId,
cast(p.xpath + '/' + c.CategoryName as varchar(max)) as xpath
from categories c inner join cte p on p.CategoryId = c.ParentId
)
select xpath from cte
order by xpath

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kashif on: 9/1/2013 [Member] Starter | Points: 25

Up
0
Down
This is the Final Solution of My problem...


Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/1/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Kashif,

Can you mark replies (Posted on: 8/1/2013 6:28:11 AM , Posted on: 8/22/2013 6:36:39 AM ) as answered...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kashif, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response