Inner join same table in SQL Server

Question

I checked this page INNER JOIN same table for my answer. I got part of it.

I need select id and referral link of parent(main) account which has less 7 referrals. I have this query, but it works for all parent(main) account (all account has same amount of referrals)

select 
      id, 
      referralLink 
   from 
      accs
   where 
      ( 3 > (select 
                   count(*) 
                from 
                   accs as acc 
                      join accs as mainAcc 
                         on mainAcc.id = acc.mainID 
                 where
                    acc.paymentCompleted = 1) 
      and referralLink is not null)

My table:

Answer 1

Yes, doing a self-join from the ID to the main, but not nested within the outer. This might be closer to what you are looking for.

select 
      a.id, 
      a.referralLink,
      count(*) ChildRecs
   from 
      accs a
         join accs child
            on a.id = child.mainid
   where 
      a.referralLink is not null   
   group by
      a.id, 
      a.referralLink
   having 
      count(*) > 3

From your sample data, the PARENT ID is the ID column. The CHILD records are the ones pointing to a "MainID" of the parent. In the sample of your data, Parent "ID" of 4, but the child ID entries of 5 through 13 are all associated with the MAIN ID of 4.

So, in this case, the returned result of this query would show

ID  ReferralLink      ChildRecs
4   3adc0039f8ff2970  9

No other IDs would be returned since the parent "ID" have no entries with MAINID pointing to anything else. Id #s 4, 36 and 38 all appear to be parents, but no additional data shows child entries with MAINID populated otherwise.

Now, based on your issue of having LESS than 7, just change the HAVING clause portion to what you want. You could remove the HAVING clause just to see what IS returned... or even having count(*) > 2 just to see SOMETHING with child entries for a given parent