sql select with exact outcome
- by Shiro
Asking a simple question, just want everyone have fun to solve it.
I got 2 tables.
1. Student
2. Course
Student
+----+--------+
| id | name |
+----+--------+
| 1 | User1 |
| 2 | User2 |
+----+--------+
Course
+----+------------+------------+
| id | student_id | course_name|
+----+------------+------------+
| 1 | 1 | English |
| 2 | 1 | Chinese |
| 3 | 2 | English |
| 4 | 2 | Japanese |
+----+------------+------------+
I would like to get the result all student, who have taken English and Chinese, NOT English or Chinese.
Expected result:
+----+------------+------------+
| id | student_id | course_name|
+----+------------+------------+
| 1 | 1 | English |
| 2 | 1 | Chinese |
+----+------------+------------+
What we normally do is
select * from student join course on (student.id = course.student_id) WHERE course_name = 'English' OR course_name = 'Chinese'
but in this result I can get User2 record which is not my expected result. I want the record only display the User take the course English+Chinese only.
