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;
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