MYSQL -获得由1和2老师提供的课程名称。

Question

如果您有以下表格：

   course:
   +---------------+---------------+
   |      id       |     Name      |
   |---------------|---------------| 
   |      1        |   Maths       | 
   |      2        |   English     |        
   |      3        |   Art         |        
   |      4        |   Physics     |        
   |      5        |   Psychology  |        
   +-------------------------------+        

   course_teacher:                                   
   +----------------------+---------------------+        
   |      course_id       |     teacher_id      |
   |----------------------|---------------------|
   |      1               |   1                 |
   |      2               |   1                 |
   |      1               |   2                 |
   |      2               |   2                 |
   |      3               |   2                 |
   +----------------------v---------------------+

我怎样才能得到由1和2老师给出的课程名称清单。

我尝试过的：SELECT DISTINCT(course_id) FROMcourse_teacherWHEREteacher_idIN (1,2);

但是，该查询将过滤所有的双重结果，而不是对教师1和2进行筛选。

Answer 1

SELECT DISTINCT(name) FROM courses c
INNER JOIN course_teachers ct ON c.id = ct.course_id 
WHERE ct.teacher_id = 1 OR ct.teacher_id = 2;

http://sqlfiddle.com/#!2/8f7f9/6/0

如果你只想要两位老师的课程，那么它是相关的：

SELECT name FROM courses c
INNER JOIN course_teachers ct ON c.id = ct.course_id 
WHERE ct.teacher_id = 1 OR ct.teacher_id = 2 GROUP BY c.id 
HAVING COUNT(c.id) = 2;

http://sqlfiddle.com/#!2/8f7f9/7/0

Answer 2

我现在找到它了，JOIN表：

SELECT vk1.`course_id` 
FROM `course_teacher` vk1, `course_teacher` vk2
WHERE vk1.`teacher_id` =2
AND vk2.`teacher_id` =10
AND vk1.`course_id` = vk2.`course_id` 
GROUP BY vk1.`course_id` 

请注意，我没有包括课程名称！这需要额外的JOIN