复杂的SELECT SQL语句(Complex SELECT SQL statement)

 有一些课程。 课堂上的学生学习科目。 我将这些表格连接到每个表格上：对于1个班级，将有许多科目，对于1个科目，将有许多班级。 Subject M <=> M Class 。 所以我在我的数据库中有3个表来创建这些关系： 
 
 学科： 
 
+----+------+
+ id + name +
+----+------+
 
 类： 
 
+----+-------+------+
+ id + digit + char +
+----+-------+------+
 
 ClassVsTeacher（连接这两个表）： 
 
+----+----------+------------+
+ id + class_id + subject_id +
+----+----------+------------+
 
 TeacherVsClass（对于第二个问题很重要，可以连接课程和教师）： 
 
+----+----------+------------+
+ id + class_id + teacher_id +
+----+----------+------------+
 
 1）我是对的吗？ 
 
 2）我正在努力将课程添加到课堂上。 我想在下拉列表中显示至少有1位老师的所有科目，除了已经连接的科目。 
 
 我知道如何展示至少有一位老师的所有科目： 
 
select 
    `s`.`id`, `s`.`name`
from `Subject` as `s`
where 
    `s`.`id` in (select `subject_id` from `TeacherVsSubject`)
 
 它工作正常，但我无法理解如何显示这个，除了已经连接到当前类的主题。 这是我的问题。 

There are some classes. Students in the classes learn subjects. I connected these tables to each over so: For 1 class there will be many subjects, for 1 subjects there will be many classes. Subject M <=> M Class. So I have 3 tables in my database to create these relationships:
 
Subject:
 
+----+------+
+ id + name +
+----+------+
 
Class:
 
+----+-------+------+
+ id + digit + char +
+----+-------+------+
 
ClassVsTeacher (to connect these 2 tables):
 
+----+----------+------------+
+ id + class_id + subject_id +
+----+----------+------------+
 
TeacherVsClass (important for 2nd question, to connect classes and teachers):
 
+----+----------+------------+
+ id + class_id + teacher_id +
+----+----------+------------+
 
1) Am I right?
 
2) I am working on adding subject to a class now. I want to show in drop-down all subjects which has at least 1 teacher, except subjects current class already connected with.
 
I know how to show all subjects which has at least 1 teacher:
 
select 
    `s`.`id`, `s`.`name`
from `Subject` as `s`
where 
    `s`.`id` in (select `subject_id` from `TeacherVsSubject`)
 
It works fine, but I can't understand how to show this, except subjects which is already conneted to current class. This is my question.

原文：https://stackoverflow.com/questions/21226640