How to insert values into a table from two different tables?

Question

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?

Answer 1

You would write the query like this

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', t.id, s.id
from teachers t,students s
where t.teacher_name = 'david'
and s.student_name = 'sam';

Be careful. This is a Cartesian product. Another way to approach this is

select teacher_id into @tid from teachers where teacher_name = 'david';
select student_id into @sid from students where student_name = 'sam';
insert into classroom (date, teacher_id, student_id) values ('2014-07-08',@tid,@sid);

• Baba Kamdev: Thank you sir, can i use inner join here?
• RolandoMySQLDBA: No need for an INNER JOIN since teachers and students have no foreign key relationship.

Answer 2

The easiest way you can do this is using sub queries:

 INSERT INTO classroom(teacher_id,student_id)
 VALUES ((SELECT id FROM students WHERE s_name='sam'),
 (SELECT id FROM teacher WHERE t_name='david'));

Answer 3

INSERT INTO newtable(value1, value2, value3) 
SELECT value1N, value2N, value3N,(SELECT valueN4 FROM secondtable WHERE id='1') 
FROM firsttable WHERE id='1');

This will put the result form firsttable value1N, value2N, value3N and the result from secondtable valueN4

Result:

• first table---|username|password |name|---(has 3 values, but we use one)
  
• second table---|id_number|Adress|tel|---(has 3 values, we use all)
  
• newtable after query will be fill ---|id_number|Adress|tel|username|-----(we get 4 values: 3 from second table and 1 from first table: