Question

好吧,我知道标题可能有点不清楚, 但我找不到更好的标题。让我解释一下情况,

I have 3 tables (actually many, but for this example we need 3).
1. teachers table with columns teacherid, teachername, other columns (DOB etc)...
2. classes table with columns classid, classname, other columns (students in class etc)
3. subjects table with struct subjectid, subjectname, other columns (unimportant at this point)

now as you can probabably guess from tables, that there exists a many to many relationship, as one teacher can teach many classes, also, one teacher can teach many subjects, and one class can have many subjects etc etc. So the tables that contain these many to many relationship are..
4. ClassSubject columns ClassId, SubjectId, CustomRemark
5. TeacherSubject columns TeacherId, SubjectId, CustomRemark
6. TeacherClass columns TeacherId, ClassId, CustomRemark

您可能再次猜想, 表 4 代表了哪个类有哪些科目的地图( 前两个栏是标记为主键 ) 。表 5 代表了教师可以教授什么科目( 前两个栏是标记为 pk ), 再说是表 6 代表了教师可以教授什么课程。 (我希望我足够明确,不会混淆您! )

现在的问题是,在某个点上,我有这个字典, Dicatary<int, dictionary<int, List<int> & gt; & gt; dctClsSubTeachers , 以给定顺序、类的代号、班级的代号、另一本包含主题代号的词典, 以及班级代号, 如果课程中有一份可以在相应类中教授该科目的教师名单, 则该词典会变得有点复杂。 (这听起来可能有点复杂, 但请仔细考虑一下, 我确信它会有意义 : ()

so, 我需要填入此字典, 因此我需要一个查询想法, 我可以提供类Id 和主题Id 作为参数, 我得到一份所有教师的名单, 这些教师不仅可以教授该科目, 而且可以在参数给定的班级中教授该科目, (我们有表 4、 5和 6, 关系), 因此我可以填入该词典。

我尝试过很多方法,但都行不通, 我尝试过的一对夫妇。

select teacherId from teachers where teacherid in (SELECT teacherid from teacherclass where classid = k
intersect
SELECT teacherid from teachersubject where subjectid = k2) // I can t use this because access doesn t support intersect

另一件我试过了

SELECT teacherid
FROM Teachers, (ClassSubject INNER JOIN TeacherClass ON ClassSubject.ClassId = TeacherClass.ClassId) INNER JOIN TeacherSubject ON ClassSubject.SubjectId = TeacherSubject.SubjectId;

我尝试过几次,但不能做,在头痛10小时后,我以为是堆积如山!所以,请大家帮帮我,好吗?

ps : If you need any further clarification about database (or anything else) please feel free to ask..
pps : I am using ms-access 2007 and no I can t migrate to sql server due to some reason unimportant at this discussion

Answer 1

Yeah!!! I have solved it myself actually! Here s the solution..

SELECT Classes.ClassId, Classes.ClassName, Subjects.SubjectId, Subjects.SubjectName, Teachers.TeacherId, Teachers.TeacherName
FROM (Teachers INNER JOIN (Classes INNER JOIN TeacherClass ON Classes.ClassId = TeacherClass.ClassId) ON Teachers.TeacherId = TeacherClass.TeacherId) INNER JOIN (Subjects INNER JOIN TeacherSubject ON Subjects.SubjectId = TeacherSubject.SubjectId) ON Teachers.TeacherId = TeacherSubject.TeacherId
where classes.classid = k and subjects.subjectid = p
GROUP BY Classes.ClassId, Subjects.SubjectId, Teachers.TeacherId, classes.classname, subjects.subjectname, teachers.teachername;

Answer 2

尝试使用 JOIN :

SELECT tc.TeacherId
FROM ClassSubject
INNER JOIN TeacherSubject ON TeacherSubject.SubjectId = ClassSubject.SubjectId
INNER JOIN TeacherClass ON TeacherClass.TeacerId = TeacherSubject.teacherId AND ON TeacherClass.ClassId = ClassSubject.ClassId
WHERE ClassSubject.ClassId = [Your ClassId]
AND ClassSubject.SubjectId = [Your SubjectId]

Answer 3

试试这个

Select TeacherSubject.TeacherId From TeacherSubject 
INNER JOIN ClassSubject ON TeacherSubject.SubjectID=ClassSubject.SubjectID
INNER JOIN TeacherClass ON Teacherclass.ClassID=TeacherSubject.ClassID
Where TeacherClass.ClassID=1 AND ClassSubject.SubjectID=1

当然是MSS SQL 语法。我认为,对于MS Accesses,你必须使用JOIN。要从教师班重新获得教师姓名,你需要加入教师身份证明。

友情链接