12

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?

13

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);
  • Thank you sir, can i use inner join here? – Baba Kamdev Jul 18 '14 at 17:57
  • No need for an INNER JOIN since teachers and students have no foreign key relationship. – RolandoMySQLDBA Jul 18 '14 at 18:13
6

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'));
1
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.