[SOLVED] Problem in handling SQL query, referrer problem

Issue

I have encountered a problem when I am working on an assignment. I want to display the member_ID, name and num_of_referee of the member(s) who have
referred more than 2 other members. The code was compiled, but nothing returned. I am not sure which part goes wrong.

SELECT m.member_ID, m.name, COUNT(*)
FROM member m    
HAVING (m.member_ID, COUNT(*)) IN (
    SELECT m.referrer_ID, COUNT(*)
    FROM member m
    WHERE m.referrer_ID IS NOT NULL
    GROUP BY m.referrer_ID
    HAVING COUNT(*) > 2)
ORDER BY m.member_ID DESC

Attributes of the database

• Branch (branch_ID, name, address_street, address_district)
Foreign key: None

• Instructor (instructor_ID, name, year_of_experience)
Foreign key: None

• Class (class_ID, name, description, date, capacity, branch_ID, instructor_ID)
Foreign key:
{branch_ID} referencing Branch.branch_ID;
{instructor_ID} referencing Instructor.instructor_ID

• Member (member_ID, name, date_of_birth, referrer_ID)
{referrer_ID} referencing Member.member_ID
Note: A member may be referred by none or at most one other member. A member can refer many other members to the fitness company.

• Enrollment (member_ID, class_ID)
Foreign key:
{member_ID} referencing Member.member_ID;
{class_ID} referencing Class.class_ID

Solution

Perhaps a different way where you calculate the referrals then join to get member eg

create table t
(member_ID int, name varchar(10),referrer_ID int);

insert into t values
(1,'one',null),
(2,'two',3),(3,'three', null),(4,'four',3);

with cte as
(    SELECT m1.referrer_ID ,count(*) as cnt
    FROM t m1
    WHERE m1.referrer_ID IS NOT NULL
    GROUP BY m1.referrer_ID
    HAVING COUNT(*) >= 2)
    
select t.member_id, t.name, cnt
from   cte 
join   t on t.member_id = cte.referrer_id;

+-----------+-------+-----+
| member_id | name  | cnt |
+-----------+-------+-----+
|         3 | three |   2 |
+-----------+-------+-----+
1 row in set (0.001 sec)

Answered By – P.Salmon

Answer Checked By – Cary Denson (BugsFixing Admin)