I have three columns, I want to combine them all in one 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 id in one table.

Can you please tell me how I can get all three ids into 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 are:

• Table 1 contains: student_id column
• Table 2 contains: teachers_id
• Table 3 contains: class_id

Main table is table 4 which will have columns:

student_id, teachers_id, class_id 

I am trying to generate time table, I want to get student_id, teachers_id and class_id into the 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

Thank you

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..