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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	-- 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

-- 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

0	;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

;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

0	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

0	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

0	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

0	; 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

; 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

0	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

0	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

Latest Posts