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:

[Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) — Ken White, Mar 16 '23 at 04:22

score 2 · Answer 1 · answered Mar 16 '23 at 04:26

Looks like you need to aggregate the records by the referrer / parent / main id and then select the records that match the conditions (id = mainid and count < 7).

You need a group by and then a count.

You will need to join back to a sub-query that aggregates the records.

The query would look like this:

SELECT id, referralLink
FROM 
    accs
    INNER JOIN
    (
        SELECT mainId AS parent, count(*) as num_referral
        FROM accs
        GROUP BY mainId
    ) referral_counts ON referral_counts.parent = accs.id
WHERE
    num_referral < 7

score 1 · Accepted Answer · answered Mar 16 '23 at 04:24

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

Inner join same table in SQL Server

2 Answers2