Querying Students Enrolled in Both Course 1 and Course 2 with MySQL

When you need to list students who have taken both course 1 and course 2, a straightforward WHERE course_id IN (1,2) query can return extra rows because it also includes students who have taken only one of the courses.

The example creates a table t_student_course with columns id, course_id, and stu_name, and inserts sample data where only student Tom is enrolled in both courses.

CREATE TABLE `t_student_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `course_id` int(11) NOT NULL COMMENT '课程id',
  `stu_name` varchar(32) NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_student_course` VALUES ('1','1','Tom');
INSERT INTO `t_student_course` VALUES ('2','2','Tom');
INSERT INTO `t_student_course` VALUES ('3','1','John');
INSERT INTO `t_student_course` VALUES ('4','3','John');
INSERT INTO `t_student_course` VALUES ('5','1','Jerry');

A naive query such as:

SELECT stu_name FROM t_student_course WHERE course_id IN (1,2) GROUP BY stu_name;

returns Tom , John , and Jerry because it groups by name before ensuring each student appears in both courses.

The correct approach adds a HAVING clause that counts the distinct course_id values per student and keeps only those with a count of 2:

SELECT stu_name FROM t_student_course WHERE course_id IN (1,2)
GROUP BY stu_name
HAVING COUNT(DISTINCT course_id) = 2;

This query correctly returns only Tom , the student enrolled in both specified courses.

The article concludes with a friendly request for readers to like, share, and comment to help the author reach a wider audience.