Click here to Skip to main content
15,066,651 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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
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;

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
Updated 26-May-12 4:02am

Not sure if I understood the question correctly, but can you simply join the tables and then apply proper conditions. Something like
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;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900