This guide demonstrates how to retrieve students belonging to specific clubs using SQL's has-many-through relationship. We'll assume three tables: student
, club
, and student_club
(the join table). The goal is to find students who are members of both the soccer club (ID 30) and the baseball club (ID 50).
The solution employs a concise and efficient SQL query:
<code class="language-sql">SELECT DISTINCT s.id, s.name FROM student s JOIN student_club sc ON s.id = sc.student_id WHERE sc.club_id = 30 AND s.id IN (SELECT student_id FROM student_club WHERE club_id = 50);</code>
This query leverages a subquery for optimal performance. Let's break it down:
JOIN
Clause: The student
and student_club
tables are joined using the student_id
to link students to their club memberships.
WHERE
Clause: This clause filters the results. The first condition (sc.club_id = 30
) ensures we only consider students in the soccer club. The second condition (s.id IN (...)
) uses a subquery to further refine the results, including only students whose IDs are also present in the student_club
table for the baseball club (club_id = 50).
This approach avoids unnecessary complexity and efficiently utilizes database indexes (if available on student_id
and club_id
columns) to quickly retrieve the desired results, even with extensive datasets. The DISTINCT
keyword ensures that each student is listed only once, even if they have multiple entries in the student_club
table.
The above is the detailed content of How to Find Students Belonging to Multiple Clubs Using SQL's Has-Many-Through Relationship?. For more information, please follow other related articles on the PHP Chinese website!