How To Count MySQL Results In A Has-many-through Relation

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, no

Solution 1:

Probably simplest, cleanest and fastest for just two students:

SELECT count(*) AS ct
FROM   student_club x
JOIN   student_club y USING (stud_id)
WHERE  x.club_id = 30
AND    y.club_id = 50;

You don't need to join to the student table for this at all - as soon as you know the list of stud_id you are interested in.

For any number of students, Martin's query is more convenient. You can simplify in a similar fashion:

SELECT count(*) AS ct
FROM (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING count(*) = 2 -- adapt to number of items in list
   ) x;

Requires that (stud_id, club_id) is unique of course, and that list items are also unique.

Solution 2:

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;