Problem in handling SQL query, referrer problem-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

For some reason, with clause isn't working on my version of mySQL. I somehow tried this and works. I post it here for reference.

SELECT member.member_ID, member.name, cnt
FROM member JOIN (
    SELECT referrer_ID, count(*) as cnt
    FROM member
    WHERE referrer_ID IS NOT NULL
    GROUP BY referrer_ID
    HAVING cnt > 2) as temp
WHERE member.member_ID = temp.referrer_ID
ORDER BY member.member_ID DESC