SQL Server - nested select query

I've following table data
Cid    fname    lname    emailid        refid
1       Peter    H    Peter@h.com      0
2       A    A    a@a.com                 1
8       B    B    b@b.com                 2
9       c    c     c@c.com                  8
10     D    D   d@d.com                 9
11     e    e     e@e.com                 2
12     f    f      f@f.com                  10
I want data till level 3 means here 8.    For illustrative purposes, assume that A introduces B and E. B introduces C and F. C introduces D. D introduces G. We recommend candidate D to our client

so i want till level three data means
Cid    fname    lname    emailid        refid
1    Peter    H    Peter@h.com       0
2    A         A    a@a.com             1
8    B         B    b@b.com             2
9    c          c    c@c.com              8
10  D         D    d@d.com            9

for run query i've refferid of 0 and last refferce id
so how should i write a query to fatch the above records?

I'm using following query but it gives only one last level row

select * from TbMLM_HcsCandidates as a
 where referrer_id in (select b.candidate_id from TbMLM_HcsCandidates as b
 where referrer_id in(select c.Candidate_id from TbMLM_HcsCandidates as c
 where referrer_id=0))

Thanks in advance

Either I misunderstand your question or the result set in your example is wrong. When I use a recursive Common Table Expression:

DECLARE @Referrer int;
SET @Referrer = 0;

WITH Referrers AS
(SELECT Cid, fname, lname, emailID, RefID, 0 AS RLevel
 FROM dbo.TbMLM_HcsCandidates
 WHERE refid = @Referrer
 UNION ALL
 SELECT c.Cid, c.fname, c.lname, c.emailID, c.RefID, r.Rlevel + 1
 FROM dbo.TbMLM_HcsCandidates c JOIN Referrers r ON c.refid = r.Cid)
 SELECT Cid, fname, lname, emailID, RefID
 FROM Referrers
 WHERE RLevel <= 3
 ORDER BY Cid;

I get Cids 1, 2, 8, 9 ,and 11 in the result set.

Just in case you are using SQL Server 2000 instead of SQL Server 2005 or higher, you can create a function that will show the hierarchy level of each record.  A similar recursive function is discussed on the following link:

http://www.sql-server-helper.com/functions/get-tree-node-level.aspx

Here's the function in that link and you can just modify it to suit your table structure.

CREATE FUNCTION [dbo].[ufn_GetTreeNodeLevel] ( @pCurrentNode    INT )
RETURNS INT
AS
BEGIN

    DECLARE @vParentID            INT

    IF @pCurrentNode = 0 OR @pCurrentNode IS NULL
        RETURN 0

    SELECT @vParentID = [ParentID]
    FROM [dbo].[Hierarchy]
    WHERE [ID] = @pCurrentNode

    RETURN [dbo].[ufn_GetTreeNodeLevel] ( @vParentID ) + 1

END
GO

To use it, all you have to do is call it like this:

SELECT * FROM TbMLM_HcsCandidates where [dbo].[ufn_GetTreeNodeLevel] ( [CID] ) <= 3

Regards,
http://www.sql-server-helper.com/sql-server-2008/index.aspx

this is a best answer on eggcafe.Thanks a lot. you should get 100% for this wonderful answer.
this is a best answer on eggcafe.


Thanks a lot unlimited and tremendously