Pyraimid Qurey Help Please?

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!