2018-09-12 07:32



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.enter image description here 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.


  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • douzhi4311 douzhi4311 3年前

    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 
     ,teacher_id int references teacher(teacher_id),class_id int references 

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

    点赞 评论 复制链接分享
  • duan7772 duan7772 3年前

    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 (
     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:

     class_per_student cps
     INNER JOIN class c ON = 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

    点赞 评论 复制链接分享
  • douwei9973 douwei9973 3年前
    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
    点赞 评论 复制链接分享