How to get category and all its parent category records at a time [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

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

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

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