Pyraimid Qurey Help Please? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pyraimid Qurey Help Please?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2008-04-17 : 15:25:45

Hi,

I am working on a multi level payout system with a parent child table for referrers. I am performing multiple In Lists to get to level 3 of the pyramid as you can see by running the code below.

I would like if this is the best way to refer to these folks down on level 3 and is there a better way to go down the levels.

Any help would be greatly appreciated. Thanks.

Here is my code:


-- Make the Table
CREATE TABLE [dbo].[x_Test__Member_Refer](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NOT NULL,
	[MemberID] [int] NOT NULL,
	[dtmTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_MemberRefer_dtmTimeStamp]  DEFAULT (getdate()),
 CONSTRAINT [PK_Member_Refer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Add some Data
Insert Into x_Test__Member_Refer ( CustomerID, MemberID )
Values ( 59108, 59107 )

Insert Into x_Test__Member_Refer ( CustomerID, MemberID )
Values ( 59109, 59107 )

Insert Into x_Test__Member_Refer ( CustomerID, MemberID )
Values ( 59110, 59107 )

Insert Into x_Test__Member_Refer ( CustomerID, MemberID )
Values ( 59123, 59109 )

Insert Into x_Test__Member_Refer ( CustomerID, MemberID )
Values ( 59126, 59123 )

-- Run our Queries
Select * From x_Test__Member_Refer

-- Set Top Member
Declare @MemberID int
Set @MemberID = 59107

-- Select Level 1
Select *
From dbo.x_Test__Member_Refer
Where MemberID = @MemberID

-- Select Level 2
Select *
From dbo.x_Test__Member_Refer mr
Where MemberID In ( 
					Select CustomerID 
					From dbo.x_Test__Member_Refer
					Where MemberID = @MemberID ) 
-- Select Level 3
Select *
From dbo.x_Test__Member_Refer mr
Where MemberID In (
		Select CustomerID
		From dbo.x_Test__Member_Refer
		Where MemberID In ( 
						Select CustomerID 
						From dbo.x_Test__Member_Refer
						Where MemberID = @MemberID ) )

-- Clean Up Shop!
Drop Table x_Test__Member_Refer

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
› As far as myself... I do this for fun!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-17 : 15:51:53

have you tried this using a hierarchical CTE?


;WITH myCTE(<COLUMNS>, Level)
AS
(
Select <COLUMNS>, 0 as Level
From dbo.x_Test__Member_Refer
Where MemberID = @MemberID
UNION ALL
Select <COLUMNS>, Level + 1
From dbo.x_Test__Member_Refer
join myCTE on dbo.x_Test__Member_Refer.CustomerID = myCTE.MemberID 
)
SELECT * FROM myCTE

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources