I have three tables
students table
------------------------------------
id(PK, A_I) | student_name | nationality
teachers table
------------------------------------
id(PK, A_I) | teacher_name | email
classroom table
----------------------
id(PK, A_I) | date | teacher_id(FK to teachers.id) | student_id(FK to students.id)
If I was given teacher's name (david
for example) and student_id (7
for example) and asked to insert the teacher_id
into the classroom
table based on the id
in the teachers
table, I would do :
insert into classroom (date, teacher_id, student_id)
select '2014-07-08', id, 7
from teachers
where teacher_name = 'david';
Now, what if I was not given the student's id directly and given only the name of the student? Suppose I was given teacher's name 'david' and student's name 'sam'. How do I get the teacher_id
from teachers
table and also student_id
from the students
table and insert both into the classroom
table based on their respective names?
Best Answer
You would write the query like this
Be careful. This is a Cartesian product. Another way to approach this is