Sql Server2005 query problem

Question

i have a table which contains the following fields

Supervisorid
Empid

This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..

I want a query to get the total no of down line members under a guy say 3

Consider re-wording/formatting your question. Very difficult to understand. — RPM1984
– RPM1984, Commented Oct 22, 2010 at 4:42
Thanks @OMG Ponies. :) Still hard to understand the second paragraph tho (not because of formatting, but because of poor wording). — RPM1984
– RPM1984, Commented Oct 22, 2010 at 4:43

Adriaan Stander · Accepted Answer · 2010-10-22 04:45:10Z

3

You can make use of Recursive CTE.

Something like this

DECLARE @Table TABLE(
        Supervisorid INT,
        Empid INT
)

INSERT INTO @Table SELECT 3, 4
INSERT INTO @Table SELECT 3, 5
INSERT INTO @Table SELECT 3, 8

INSERT INTO @Table SELECT 4, 9
INSERT INTO @Table SELECT 4, 10
INSERT INTO @Table SELECT 4, 11

INSERT INTO @Table SELECT 8, 12
INSERT INTO @Table SELECT 8, 13

DECLARE @ID INT
SELECT  @ID = 3

;WITH Vals AS (
        SELECT  *
        FROM    @Table
        WHERE   SuperVisorID = @ID
        UNION ALL
        SELECT  v.SuperVisorID,
                t.Empid
        FROM    Vals v INNER JOIN
                @Table t    ON  v.Empid = t.Supervisorid
)
SELECT  SuperVisorID,
        COUNT(Empid) Total
FROM    Vals
GROUP BY    SuperVisorID

answered Oct 22, 2010 at 4:45

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql Server2005 query problem

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related