How to simplify same table nested query

Question

I'm trying to count 13th(Nth) level chield by parent id and my query is given below. but it's taking too long time for execution. is there any simpler way to do this?

The query is:

SELECT @count=COUNT(*) FROM member_introducer WHERE introducer_id IN(
 SELECT member_id FROM member_introducer WHERE introducer_id IN(
  SELECT member_id FROM member_introducer WHERE introducer_id IN(
   SELECT member_id FROM member_introducer WHERE introducer_id IN(
    SELECT member_id FROM member_introducer WHERE introducer_id IN(
     SELECT member_id FROM member_introducer WHERE introducer_id IN(
      SELECT member_id FROM member_introducer WHERE introducer_id IN(
       SELECT member_id FROM member_introducer WHERE introducer_id IN(
        SELECT member_id FROM member_introducer WHERE introducer_id IN(
         SELECT member_id FROM member_introducer WHERE introducer_id IN(
          SELECT member_id FROM member_introducer WHERE introducer_id IN(
           SELECT member_id FROM member_introducer WHERE introducer_id IN(
            SELECT member_id FROM member_introducer WHERE introducer_id = @parentId))))))))))))

Is this solution what you looking for; recursive self join; http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server — Raybarg, May 13 '15 at 09:09
Hi ughai1! thanks for response but i'm looking for level specific child not for all chields. — Amit Mishra, May 13 '15 at 09:13
This is a *recursive* or *hierarchical* query, not a nested query. If you search with the correct terms you'll find many answers and tutorials. CTEs are one way to write recursive queries but you can also add a [HierarchyId](https://msdn.microsoft.com/en-us/library/bb677173.aspx) column to simplify queries and improve performance. Asking for results at a specific level is also much easier when using hierarchies — Panagiotis Kanavos, May 13 '15 at 09:16

ughai · Accepted Answer · 2015-05-13T09:29:08.823

You can use a recursive CTE with a lvl column to denote the child level. Something like this.

Sample Data

DECLARE @member_introducer TABLE
(
member_id int,
introducer_id int
)

insert into @member_introducer VALUES(1,NULL)
insert into @member_introducer VALUES(2,1)
insert into @member_introducer VALUES(3,1)
insert into @member_introducer VALUES(4,2)
insert into @member_introducer VALUES(5,2)
insert into @member_introducer VALUES(6,3)
insert into @member_introducer VALUES(7,4)
insert into @member_introducer VALUES(8,5)
insert into @member_introducer VALUES(9,7)
insert into @member_introducer VALUES(10,8)
insert into @member_introducer VALUES(11,9)
insert into @member_introducer VALUES(12,10)
insert into @member_introducer VALUES(13,12)
insert into @member_introducer VALUES(14,13)
insert into @member_introducer VALUES(15,14)
insert into @member_introducer VALUES(16,15)
insert into @member_introducer VALUES(17,16)
insert into @member_introducer VALUES(18,17)
insert into @member_introducer VALUES(19,18)
insert into @member_introducer VALUES(20,19)

Query

DECLARE @level int = 13;


;WITH CTE as
(
  SELECT CONVERT(INT,1) lvl,P.member_id
  FROM @member_introducer P
  WHERE P.introducer_id = 1

  UNION ALL

  SELECT CONVERT(INT,M.lvl + 1) lvl,P1.member_id
  FROM @member_introducer P1  
  INNER JOIN CTE  M
  ON M.member_id = P1.introducer_id
 )
SELECT COUNT(*)
FROM CTE 
WHERE lvl = @level

If your recursive level is fixed you can even use OPTION (MAXRECURSION 13) as suggested by Zohar Peled

How to simplify same table nested query

1 Answers1