trouble in logical table as CATTREE

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

General SQL Server Forums

New to SQL Server Programming

trouble in logical table as CATTREE

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-11-03 : 23:05:07

Hello,

I have to find users in there downline, I have find out but sorting is another type.
as:

user-sponsor-Level
~~~~~~~~~~~~~~~~~~~~~~~~
a-b-1
b-c-2
c-d-3
c-e-3
c-f-3
c-g-3
b-h-2
h-H1-4
h-H2-4
b-i-3
b-j-3
j-J1-4
j-J2-4
a-A1-1
a-A2-1
A-A3-1

Procedure:

WITH CatTree (vaid, vspID,cplacement, damtpaid, Level,dtdoj,cstatus)
AS
(

SELECT vaID, vspID,cplacement,damtpaid, 1 As [Level] ,dtdoj,m.cstatus
FROM members m join scratch s on m.vaid=s.vat_id
join packages p on s.vpackid=p.vid
WHERE vspid=@sid and cplacement='L'

UNION ALL

SELECT C.vaid, C.vspID,C.cplacement,C.damtpaid, Level + 1 , c.dtdoj,c.cstatus
FROM members C
INNER JOIN CatTree ON
CatTree.vaid= C.vspid
join scratch s1 on c.vaid=s1.vat_id
join packages p1 on s1.vpackid=p1.vid

)
SELECT * FROM CatTree
where cstatus='V'
Order By vspid,level,vaid
-------------------------------------

I have find downline but not get downline as i required.
problem in sorting, please help me to find it...

Thankn you

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-11-04 : 02:02:43

help me to sortout downline

Ved Prakash Jha