I have this problem:
I have a "school" database and need to reference the Teachers table with the Subject table.
Teachers will instruct more than one subject. And here is the problem.
SELECT * FROM Teachers WHERE id_Subject = 1
What I think you're trying to describe is called a "many to many" relationship. It requires an intermediary table to connect the entity tables, and in many cases the intermediary table becomes a business entity in and of itself.
Consider for example:
Teachers ---------- ID Name etc. Subjects ---------- ID Name etc. TeacherSubjects ---------- ID TeacherID (FK to Teachers) SubjectID (FK to Subjects)
The relationship between the
Teachers and the
Subjects itself becomes a place to potentially store data. Thinking about the subject domain (a school), the
TeacherSubjects table sounds like it might be a
Classes table waiting to happen. Where you could put information about a given instance of a class, which is a business entity that has a teacher and a subject.