I am taking computer science and im working on database design. This is my second join table, but its essentially the same format as the first one.
#second
CREATE TABLE `Enrollment` (
course_ID SMALLINT NOT NULL,
student_ID SMALLINT NOT NULL,
finalGrade SMALLINT NOT NULL,
letterGrade CHAR(1),
FOREIGN KEY (`course_ID`) REFERENCES `Course` (`course_ID`) ON DELETE CASCADE,
FOREIGN KEY (`student_ID`) REFERENCES student (`student_ID`) ON DELETE CASCADE
) ENGINE = InnoDB;
What i need to know is how to get data from three other tables. this is what the tables contain
Course:
course_ID
Student:
student_ID
Table_1:
finalGrade
letterGrade
Student name is contained in both `Student` and `Table_1` so i assume that would be how i would get the correct data.
Basically i need to make sure that the correct student has the correct course_ID and the right letterGrade and finalGrade.
This is what my friend has for his database, its working fine, my database has things in other locations, so it would be different, but here it is anyways:
INSERT INTO enrollment
(studentID, courseID, final_grade_percent, letter_grade)
SELECT (SELECT `studentID` FROM `student` WHERE `student`.`first_name` = `Student Firstname` AND `student`.`last_name` = `Student Lastname`) ,
(SELECT `courseID` FROM `course` WHERE `course`.`Course Data` = `table 6`.`Course Data`),
`Final Grade %`, `Letter Grade`
FROM `table 6`;
any help at all would be fantastic