根据多对多关联表中的多个条件进行选择 [英] select based on mulitple condition in many to many relationshiped tables

问题描述

我有3个表(实际上很多，但在此示例中，我们需要3个表).
1. teachers表具有列teacherid, teachername, other columns (DOB etc)...
2. classes带有列classid, classname, other columns (students in class etc)
的表 3.具有结构subjectid, subjectname, other columns (unimportant at this point)
的subjects表
现在，您可能可以从表格中猜测到，存在着多对多的关系，一位老师可以教很多班，一位老师可以教很多科目，而一位班上可以有很多科目，等等.因此，包含这些表格的表格这些多对多的关系是..
4. ClassSubject列ClassId, SubjectId, CustomRemark
5. TeacherSubject列TeacherId, SubjectId, CustomRemark
6. TeacherClass列TeacherId, ClassId, CustomRemark

再次您可能会猜到，表4代表了什么类别具有什么主题的地图(前两个col被标记为主键).表5代表什么老师可以教什么科目(前两个列标记为pk)，表6再次代表什么老师可以教什么课. (我只是希望我足够明确，不要让您感到困惑！)

现在的问题是，在某一点上我有这本字典，Dictionary<int, Dictionary<int, List<int>>> dctClsSubTeachers会以给定的顺序存储类的ID，每个类的ID，另一个包含主题ID的字典以及该类的ID，如果是科目，则列出可以在相应班级中教授该科目的教师列表. (这听起来可能有点复杂，但是请仔细考虑一下，我相信它会很有意义:(

因此，我需要填充此词典，因此我需要一个查询思想，该思想可以提供classId和subjectId作为参数，并且我得到了不仅可以教授该学科而且可以教授该学科的所有教师的列表.在参数给定的类中(对于关系，我们有表4、5和6)，因此我可以填充该字典

请注意，我已经尝试了很多方法，但是它们都没有起作用，我尝试了几种方法..

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

Again you might guess, table 4 represents a map of what class have what subjects (first two col are marked primary key). table 5 represents what teacher can teach what subject(s) (first two col marked as pk), again table 6 represent what teacher can teach what classes. (I just hope I am explicit enough to not to confuse you!)

Now the problem is, at one point I have this dictionary, Dictionary<int, Dictionary<int, List<int>>> dctClsSubTeachers that stores in the given order, id of the class, for each id of class, another dictionary that contains id of subject, and for that id of the class, and that if of subject a list of teacher that can teach that subject in the corresponding class. (it might sound a little complex, but please think over it for a minute I am sure it will make sense :(

So, I need to populate this dictionary, thus I need a query thought which I can supply classId and subjectId as parameters, and I get a list of all the teacher that can not only just teach that subject, but teach that subject in the class given in parameter, (we have table 4, 5 and 6 for relationship), thus I could populate that dictionary

Just so you know, I''ve tried a lots of ways, but none of them worked, a couple that I tried are..

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

我尝试过的另一个

another one I tried

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

而且我尝试了更多，但是却做不到.所以，任何人都可以，请在这里帮助我吗?

ps:如果您需要有关数据库(或其他任何内容)的任何进一步说明，请随时询问..
pps:我正在使用ms-access 2007，并且由于某些原因在此讨论中不重要，我不能迁移到sql服务器

and I have tried a couple of more, but couldn''t do it. So, can anyone please, please help me out here?

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

推荐答案

否请确定我是否正确理解了这个问题，但是您可以简单地加入表格然后应用适当的条件吗?像

Not sure if I understood the question correctly, but can you simply join the tables and then apply proper conditions. Something like

SELECT *
FROM   Teachers       t,
       TeacherClass   tc,
       Classes        c,
       TeacherSubject ts,
       Subjects       s,
       ClassSubject   cs
WHERE  tc.TeacherId = t.TeacherId
AND    ct.ClassId   = t.ClassId
AND    ts.TeacherId = t.TeacherID
AND    ts.SubjectId = s.SubjectId
AND    cs.ClassId   = c.ClassId
AND    cs.SubjectId = s.SubjectId
AND    s.SubjectId  = @subjectid

是的，我自己解决了它，代码...

Yeah, I''ve solved it myself, the code...

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;

这篇关于根据多对多关联表中的多个条件进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！