How to count MySQL results in a has-many-through relation

Question

There is pretty good article how to filter results in a has-many relation: How to filter SQL results in a has-many-through relation

I'm just seeking a solution for COUNT result, not show them all.

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

How many students are in both CLUB1 && CLUB2?

EDIT: It would be great to use "Martin 2" method from a link below:

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

Just adding something to COUNT results.

Answer 1

The query uses table aliases for tables student_club and club. This allows to return rows only for students who are in both clubs. Then, using COUNT allows to return the number of students:

SELECT COUNT(*) AS nb
FROM student s, student_club sc1, club c1, student_club sc2, club c2
WHERE s.id=sc1.student_id AND sc1.club_id=c1.id AND c1.name="CLUB1"
AND s.id=sc2.student_id AND sc2.club_id=c2.id AND c2.name="CLUB2"

If you really want to use the "Martin 2" query, you may count the number of records this way:

SELECT COUNT(*) AS nb
FROM (
    SELECT s.stud_id, s.name
    FROM   student s
    JOIN   student_club sc USING (stud_id)
    WHERE  sc.club_id IN (30, 50)
    GROUP  BY 1,2
    HAVING COUNT(*) > 1
) tmp;