Recursive Query Using CTE in SQL Server

Dear Friends, here i am posting the replacement of cursors using recursive query with the use of CTE.

Table Creation Scripts : -


CREATE TABLE [dbo].[tb_mlm](
[ID] [int] IDENTITY(10000001,1) NOT NULL,
[User_ID] [int] NULL,
[PARENT_ID] [int] NULL,
[poss] [tinyint] NULL
) ON [PRIMARY]

GO


INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(1,null,null)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(2,1,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(3,1,1)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(4,2,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(5,2,1)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(6,3,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(6,3,1)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(7,1,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(8,1,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(9,1,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(10,1,1)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(11,2,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(12,4,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(13,4,0)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(14,4,1)
INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(15,12,1)



 CTE script to find out the left and the right count of any parent......


declare @PARENT_ID int = 1, @left int = 0, @right int = 1

;with CTE_REC([USER_ID], PARENT_ID)  ---Calucation of Left Child
AS
(
select [USER_ID], PARENT_ID from tb_mlm where PARENT_ID = @PARENT_ID and poss = @left
UNION ALL
select tb_mlm.[USER_ID], tb_mlm.PARENT_ID from tb_mlm , CTE_REC where tb_mlm.PARENT_ID = CTE_REC.[USER_ID] --and poss = @left
),
CTE_REC_RIGHT([USER_ID], PARENT_ID)    ---Calucation of Right Child
AS
(
select [USER_ID], PARENT_ID from tb_mlm where PARENT_ID = @PARENT_ID and poss = @right
UNION ALL
select tb_mlm.[USER_ID], tb_mlm.PARENT_ID from tb_mlm , CTE_REC_RIGHT where tb_mlm.PARENT_ID = CTE_REC_RIGHT.[USER_ID] --and poss = @right
),CTE_Left(Parent, count,ExactCount)   ----Combining the result into 1 line for left calculation result
AS
(select @PARENT_ID as Parent , COUNT(CTE_REC.[USER_ID]) as count, (select COUNT(*) from CTE_REC where CTE_REC.PARENT_ID = @PARENT_ID) as 'ExactCount' from CTE_REC)
,CTE_Final(Parent, count,ExactCount)    ----Combining the result into 1 line for right calculation result
AS
(select @PARENT_ID as Parent , COUNT(CTE_REC_RIGHT.[USER_ID]) as count,  (select COUNT(*) from CTE_REC_RIGHT where CTE_REC_RIGHT.PARENT_ID = @PARENT_ID) as 'ExactCount'   from CTE_REC_RIGHT)

SELECT CTE_Left.Parent, CTE_Left.count as 'TotalLeft' , CTE_Left.ExactCount as 'ExactLeftCount', CTE_Final.count as 'TotalRight' , CTE_Final.ExactCount as 'ExactRightCount'
from CTE_Left --Final Query that will contain the result
LEFT JOIN CTE_Final on(CTE_Final.Parent = CTE_Left.Parent)

Comments

GAGAN DEEP SHARMA said…
run the first script to create tables and the data into the tables.
then check out the second query.
enjoy.
June 16, 2011 at 10:10 PM
Post a Comment

Popular posts from this blog

FastEndpoint vs Minimal APIs vs Controller

Here's a tabular comparison between FastEndpoints, Minimal APIs, and Controllers in the context of ASP.NET Core: Feature/Aspect FastEndpoints Minimal APIs Controllers Design Philosophy Focuses on performance and simplicity by providing a fast, endpoint-centric approach Prioritizes minimalism and simplicity, with an emphasis on lightweight, functional programming Based on the MVC pattern, providing a structured, feature-rich approach Performance Optimized for performance with minimal overhead High performance due to minimal abstractions Slightly more overhead due to MVC and routing abstractions Setup Complexity Requires additional setup and configuration but offers more granular control Minimal setup, quickly defined routes, and handlers Requires more setup and configuration with Controllers, Actions, and Routes Routing Explicit routing defined per endpoint, more control over routes Inline routing with lambda expressions, less verbose Attribute-base...
Read more

Kick start 2021 😃😃

Welcome 2021, Good bye 2020  When many are busy in cursing year 2020 & expecting some magic to come out one number changed on 31 Dec mid night. I feel, Year 2020 was the best teacher.   Year 2020 has taught many lesson in the course of just one year. So If you survived 2020, Remember: - 1. Your body, your health There is a old saying health is wealth , that's what we saw in pandemic. Many not survived the virus but fittest survived. Another law of evolution: Survival of the fittest , Remember, No matter what, always maintain your health. You never know when we land again in another pandemic.     2. Your company, your job   If your company kept you feeding whole year in 2020, you must agree to the fact that this one is best company & show the gratitude despite you may have many complaints with your company. Make sure  we give 100% to the our job & company.  3. Single source of income  My company is great & always takes care of ...
Read more

Work with Raspberry PI Zero W

Setup Raspberry PI  https://desertbot.io/blog/headless-pi-zero-ssh-access-over-usb-windows Setup Wifi with Raspberry PI Zero https://desertbot.io/blog/headless-pi-zero-w-wifi-setup-windows Setup Camera module  https://desertbot.io/blog/how-to-stream-the-picamera
Read more