Question:
how do you insert into a table a reference to another data (MYSQL)?
2013-07-15 19:08:01 UTC
when using INSERT INTO

how do you add an information that is supposed to come from another table

say Stud_course comes from course from the course table and not the student table, how would you add a new tuple into Student given that fact?
Three answers:
Jeff P
2013-07-15 19:38:29 UTC
You would just insert the IDs from those tables.



INSERT INTO stud_course (student_id, course_id, semester_id) VALUES (133, 3323, 1394);



All of the values from student_id, course_id, and semester_id would come from those respective tables.
Ratchetr
2013-07-15 19:40:28 UTC
The short answer is that to insert into a table, you need to first know the values you want to insert (except for auto-increment primary keys). So you need to know the value of Stud_course.



The longer answer is that your table structure sounds flawed. A column called Stud_course in the course table set off a big red flag in my brain.



I assume you are doing the typical course enrollment type of homework problem.



It's easy to get started, you probably already have this part: You need a table for students, and you need a table for courses, and each table should, of course, have a primary key (most likely of the auto-increment variety).



But then you get to the harder part: How to track which students are in each course, and how to track which courses a student is taking.



There are 2 MANY-TO-MANY relationships going on here:

Each course can have MANY students.

Each student can take MANY courses.



When you run into this, you need to add a 3'rd table. A table that maps courses to students. You might call this table Stud_course. (See how I just converted your column name to a table name?)



Your Stud_course table really only needs 2 columns:

course_id

student_id



course_id is a FOREIGN key to the id column in your course table.

student_id is a FOREIGN key to the id column in your student table.



With those 3 tables, you can easily answer the typical questions with a 3 table join:

What students are taking course 123?



SELECT

FROM course

JOIN Stud_course USING(course_id)

JOIN student using(student_id)

WHERE course_id = 123;



What courses is student 456 taking?

SELECT

FROM student

JOIN Stud_course USING(student_id)

JOIN course using(course_id)

WHERE student_id = 456;



And enrolling a student in a course is as simple as:

INSERT INTO Stud_course(student_id,course_id)

VALUES ( $stud_id, $course_id);
dutra
2016-10-22 15:14:10 UTC
Create table: create table examine (identity int no longer nul auto_increment universal key, e mail varchar(50)) examine no matter if consultation contains fee or no longer.


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Continue reading on narkive:
Loading...