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

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

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

SELECT Teachers.teacher_name, Class.class_sec, Subjects.subject_name FROM timetable
INNER JOIN Teachers ON Teachers.teacher_id = timetable.teacher_id
INNER JOIN Class ON Class.class_id = timetable.class_id
INNER JOIN Subjects ON Subjects.subject_id = timetable.subject_id

As I understand it, and please correct me if I am wrong, A class has a teacher, simplifying, a teacher teaches one class or more(the more complex case is more than once teacher per class): The relation is 1 to many, so the class table should hold a teacher_id

A student can take more than one class, but there are many students in a class, then relationship is many-to-many, a new table should be created class_per_student

We need to populate them with the relevant info of course,

Once that is done, we can join all relevant tables and get the timetables

Add the teacher_id column:

ALTER TABLE class ADD COLUMN teacher_id INT(9) NOT NULL;
ALTER TABLE class ADD KEY teacher_id (teacher_id);

Create a many-to-many table

CREATE TABLE class_per_student (
 id INT(11) NOT NULL AUTO_INCREMENT,
 class_id INT(9) NOT NULL,
 student_id INT(9) NOT NULL,
 PRIMARY KEY id (id),
 KEY class_id (class_id),
 KEY student_id (student_id)
);

The time table select:

SELECT
 cps.class_id,
 cps.student_id,
 c.teacher_id
FROM
 class_per_student cps
 INNER JOIN class c ON c.id = cps.class_d
;

Notice that since the relationship between all entities is many-to-many, each of them (class, teacher, student) will appear more than once, BUT the unique combination of the 3 will appear once