How to get category and all its parent category records at a time [Resolved]

Posted by SheoNarayan under Sql Server on 9/19/2011 | Points: 10 | Views : 5182 | Status : [Administrator] | Replies : 4
Hello experts,

I have a table structure like this.

AutoId, CatName, ParentId
1, Entertainment, 0
2, Hindi Songs, 1
3, Romantic, 2
4, Technology, 0
5, C#, 4

Here ParentId = 0 is the parent category and then its sub category and so on.

I want to execute a stored procedure or SQL statement and get all its preceding categories including parent categories for Romantic. So I should be getting Entertainment, Hindi Songs records as well.

How to do this, any help is much appreciated, If difficult to understand please respond. Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com



Responses

Posted by: PandianS on: 9/20/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi Sheo

we can use Recursive CTE to perform this activity...

Creating stored procedure :
If OBJECT_ID('Usp_GetHierarchy') Is Not Null

Drop Proc Usp_GetHierarchy
Go
Create Proc Usp_GetHierarchy
(
@Category Int
)As
Begin
Set Nocount On

;With CTEs
As
(
select Parentid,Autoid,CatName from table1 where Autoid=@Category
Union All
Select a.Parentid,a.Autoid,a.CatName from table1 a, CTEs c where c.Parentid = a.autoid
)

select CatName from CTEs Order by autoid Desc
End
Go
Executing stored procedure
EXEC Usp_GetHierarchy 3

Go
Result
Romantic

Hindi Songs
Entertainment


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Dbdon on: 9/20/2011 [Member] Starter | Points: 25

Up
0
Down

--Create table for the storage of id - parent relation

CREATE TABLE pranay(
AutoID INT,
ParentID INT,
Category VARCHAR(20)
)

-- insert data into the table as required

INSERT INTO pranay (AutoID,ParentID,Category) SELECT 1, 0, 'Entertainment'
INSERT INTO pranay (AutoID,ParentID,Category) SELECT 2, 1, 'Hindi Songs'
INSERT INTO pranay (AutoID,ParentID,Category) SELECT 3, 2, 'Romantic'
INSERT INTO pranay (AutoID,ParentID,Category) SELECT 4, 0, 'Technology'
INSERT INTO pranay (AutoID,ParentID,Category) SELECT 5, 4, 'C#'

--Create a stored proc for execution with an input parameter

CREATE Procedure DBO.PranayCategory

@ID INT

AS

BEGIN
;WITH Video AS
(
SELECT *
FROM pranay
WHERE AutoID = @ID
UNION ALL
SELECT t.*
FROM pranay t INNER JOIN
video r ON t.ParentID = r.AutoID
)

SELECT *
FROM video
END

--Execute the procedure as below... change the input parameter accordingly.
exec PranayCategory 4


Hope it helps...

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

Posted by: Chvrsri on: 9/20/2011 [Member] [Moderator] [MVP] Silver | Points: 25

Up
0
Down
Hi Sheo,

I have an idea.... if my understanding is correct you need to get the records preceding records of Romantic basing on this parent id.

Here the parameter is the parent id of Romantic

for i=Parameter to 0

Select catname from tablename where parentId=parameter
parameter-1
End for


Thanks,
Radha Srikanth

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

Posted by: SheoNarayan on: 9/20/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Excellent PandianS, your code worked. Appreciate it !!!

Thanks for other two participants.

@DbDon - your code had issue so it couldn't give me the desired result
@Chvrsri - this is not I wanted, its a hierarchical data and you never know that the preceding record id will be lesser than 1

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

Login to post response