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.

Serialize

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;

Hesiod

CLUB1 and CLUB2 wath just examples. it can be 3 or more and more. it would be great to use "Martin 2" method and count results. – Serialize

Please don't change your question once it has been answered! Your current question is "How many students are in both CLUB1 && CLUB2?", and my answer solves this question. – Hesiod

Sorry, if i didn't explain it right, but for me it's not a solution. – Serialize

Answer 2

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.

Stymie

Thanks a lot. it really simplifies. – Serialize

Can you explain what means "x" at last of the query? – Serialize

@IkaPkhakadze: That's short for AS x and is the alias for the subquery. Every subquery in the FROM list needs a name in SQL. – Stymie