douju1865
douju1865
2018-09-12 07:32

我有三列,我想将它们组合在一个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.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.

Thankyou

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

3条回答

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

    点赞 评论 复制链接分享
  • 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 (
     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

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