how to insert values from multiple tables?

[SDCSA]

Hello all

I have created two tables with the following format.

Table 1

StudentId InstructorId

1000 2000
1001 2000
1002 2001
1003 2001
.... ....

Table 2

StudentId MathMarks PhysicsMarks

1000 90 80
1001 85 75
.... ... ...

Now I want StudentId InstructorId MathMarks PhysicsMarks
to be inserted as rows into a new table 3.

Any help would be appreciated.

Thanks,
sdcsa.

 

[R1972]

insert into table3(studentid, instructorid, mathmarks, physicsmarks) (select a.studentid, a.instructorid, b.mathmarks, b.physicsmarks from table2 b, table1 a where b.studentid = a.studentid)


Or

create table3 with rows...
eg.
create table table3(studentid, instructorid, mathmarks, physicsmarks) as select a.studentid, a.instructorid, b.mathmarks, b.physicsmarks from table2 b, table1 a where b.studentid = a.studentid