select based on mulitple condition in many to many relationshiped tables

Question

4.00/5 (1 vote)

See more:

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

SQL

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

SQL

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

Posted 26-May-12 3:59am

Razor4tx

Updated 26-May-12 4:02am

v2

Add a Solution

2 solutions

Solution 1

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

SQL

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

Posted 26-May-12 10:27am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Razor4tx · Accepted Answer · 2012-05-27T03:37:00

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

SQL

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;