我有三列，我想将它们组合在一个main_table中

Question

i am working in mysql, i know about tables relationships 'joins' etc, I have three different tables, each table has column id, i want to combine all three ids in one main table, because i want to apply inner join after getting all ids in one table. can you please tell me how i can get all three ids in one table, i know about foreign key and primary key, but i don't know how i can apply that here... or is there any simple method of doing it. My tables name:

Table 1 contains: student_id coloumn
Table 2 contains: teachers_id
Table 3 contains: class_id

Main table is table 4: which will have student_id,teachers_id,class_id coloumn

i am trying to generate time table, i want get student id,teachers_id and class_id, in main table time_table; i am trying to do normalization so that i don't have to repeat all name again and again, i can just use id to call any class name teacher name subject name etc, all ids are primary keys in tables.

The relationship is one to one in this case

---

i am working on php_mysql.

Thankyou

Answer 1

Create tables as follows,

 create table subject(subject_id int primary key,sub_name varchar(20))
 create table teacher(teacher_id int primary key,teacher_name varchar(20))
 create table class(class_id int primary key,class_sec varchar(20))

 create table timetable(t_id int primary key,subject_id int references 
 subject(subject_id)
 ,teacher_id int references teacher(teacher_id),class_id int references 
 class(class_id))

Inserting sample values

 insert into subject values(1,'Tamil')
 insert into teacher values(1,'Pugal')
 insert into class values(1,'12th A')
 insert into timetable values(1,1,1,1)

Using Inner join to connect tables,

 select s.sub_name,t.teacher_name,c.class_sec from timetable t1 
 inner join subject s
 on s.subject_id = t1.subject_id inner join teacher t
 on t.teacher_id = t1.teacher_id inner join class c
 on c.class_id   = t1.class_id

Try this...And revert me if any clarifications needed..