SQL Server/T-SQL Tutorial/Table Join/many to many

Implementing Many-to-Many Relationships with Foreign Keys

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

Update a many-to-many relation (student, class, classgrade)

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