How to perfom data retrival operation using Junction table in SQL?

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 953
Lets create a Student table and a Grade table.

CREATE TABLE tblStudents
(
StudentID int IDENTITY(1,1) PRIMARY KEY,
StudentName varchar(50) NOT NULL
)

CREATE TABLE tblGrades
(
GradeID int IDENTITY(1,1) PRIMARY KEY,
Grade char(1) NOT NULL
)


Now that we have our two tables created we need to create the junction table that will link them together. The junction table is created by using the primary key from the Student table abd Grade table

CREATE TABLE tblStudentGrade
(
StudentID int NOT NULL,
GradeID int NOT NULL,
CONSTRAINT PK_StudentGrade PRIMARY KEY
(
StudentID,
GradeID
),
FOREIGN KEY (StudentID) REFERENCES tblStudents (StudentID),
FOREIGN KEY (GradeID) REFERENCES tblGrades (GradeID)
)




To find out the what Student got what Grades we can use the junction table and the following query:

SELECT s.StudentName, g.Grade
FROM tblStudentGrade sg
JOIN tblStudents s ON s.StudentID = sg.StudentID
JOIN tblGrades g ON g.GradeID = sg.GradeID

Comments or Responses

Login to post response