SQL Server/T-SQL Tutorial/Table Join/many to many
Implementing Many-to-Many Relationships with Foreign Keys
<source lang="sql">
4> CREATE TABLE Students( 5> StudentID int Primary Key, 6> FirstName nvarchar(30), 7> LastName nvarchar(50), 8> FullName AS (FirstName + " " + LastName) 9> ) 10> GO 1> 2> CREATE TABLE Classes( 3> ClassID int Primary Key, 4> ClassTitle varchar(50) 5> ) 6> GO 1> 2> CREATE TABLE ClassGrades( 3> ClassID int, 4> StudentID int, 5> GradeLetter varchar(2), 6> Constraint PK_ClassGrades 7> PRIMARY KEY(ClassID, StudentID), 8> Constraint FK_Classes_ClassID 9> FOREIGN KEY(ClassID) 10> REFERENCES Classes(ClassID) ON UPDATE CASCADE, 11> Constraint FK_Students_StudentID 12> FOREIGN KEY(StudentID) 13> REFERENCES Students(StudentID) ON UPDATE CASCADE 14> ) 15> GO 1> 2> --Insert classes rows 3> INSERT Classes VALUES(1,"SQL") 4> INSERT Classes VALUES(999,"Java") 5> GO (1 rows affected) (1 rows affected) 1> 2> --Insert Students rows 3> INSERT Students VALUES(1, "Poor", "DBA") 4> INSERT Students VALUES(2, "Better", "DBA") 5> GO (1 rows affected) (1 rows affected) 1> 2> --Insert ClassGrades rows 3> INSERT ClassGrades VALUES(1, 1, "C+") 4> INSERT ClassGrades VALUES(1, 2, "A+") 5> INSERT ClassGrades VALUES(999, 2, "A") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> --Show table values after initial population 3> SELECT S.FullName, C.ClassTitle, CG.GradeLetter 4> FROM Classes C, ClassGrades CG, Students S 5> WHERE C.ClassID = CG.ClassID AND 6> S.StudentID = CG.StudentID 7> GO FullName ClassTitle GradeLetter
-------------------------------------------------- -----------
Poor DBA SQL C+ Better DBA SQL A+ Better DBA Java A (3 rows affected) 1> 2> drop table ClassGrades; 3> drop table Classes; 4> drop table Students; 5> GO</source>
Update a many-to-many relation (student, class, classgrade)
<source lang="sql">
4> CREATE TABLE Students( 5> StudentID int Primary Key, 6> FirstName nvarchar(30), 7> LastName nvarchar(50), 8> FullName AS (FirstName + " " + LastName) 9> ) 10> GO 1> 2> CREATE TABLE Classes( 3> ClassID int Primary Key, 4> ClassTitle varchar(50) 5> ) 6> GO 1> 2> CREATE TABLE ClassGrades( 3> ClassID int, 4> StudentID int, 5> GradeLetter varchar(2), 6> Constraint PK_ClassGrades 7> PRIMARY KEY(ClassID, StudentID), 8> Constraint FK_Classes_ClassID 9> FOREIGN KEY(ClassID) 10> REFERENCES Classes(ClassID) ON UPDATE CASCADE, 11> Constraint FK_Students_StudentID 12> FOREIGN KEY(StudentID) 13> REFERENCES Students(StudentID) ON UPDATE CASCADE 14> ) 15> GO 1> 2> SELECT * FROM Classes 3> GO ClassID ClassTitle
--------------------------------------------------
(0 rows affected) 1> 2> 3> --Insert classes rows 4> INSERT Classes VALUES(1,"SQL") 5> INSERT Classes VALUES(999,"Java") 6> GO (1 rows affected) (1 rows affected) 1> 2> --Insert Students rows 3> INSERT Students VALUES(1, "Poor", "DBA") 4> INSERT Students VALUES(2, "Better", "DBA") 5> GO (1 rows affected) (1 rows affected) 1> 2> --Insert ClassGrades rows 3> INSERT ClassGrades VALUES(1, 1, "C+") 4> INSERT ClassGrades VALUES(1, 2, "A+") 5> INSERT ClassGrades VALUES(999, 2, "A") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> UPDATE Classes 3> SET ClassID = 998, 4> ClassTitle = "Java" 5> WHERE ClassID = 999 6> GO (1 rows affected) 1> 2> SELECT * FROM Classes 3> GO ClassID ClassTitle
--------------------------------------------------
1 SQL
998 Java
(2 rows affected) 1> 2> drop table ClassGrades; 3> drop table Classes; 4> drop table Students; 5> GO</source>
