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

Материал из SQL эксперт
Перейти к: навигация, поиск

An one-to-many relation

   <source lang="sql">

3> CREATE TABLE Classes( 4> ClassID int PRIMARY KEY, 5> ClassTitle varchar(50) 6> ) 7> GO 1> 2> CREATE TABLE ClassGrades( 3> ClassID int REFERENCES Classes(ClassID) 4> ON UPDATE CASCADE, 5> StudentID int, 6> GradeLetter varchar(2), 7> Constraint PK_ClassGrades 8> PRIMARY KEY(ClassID, StudentID) 9> ) 10> 11> INSERT Classes VALUES(1,"SQL") 12> INSERT Classes VALUES(999,"Java") 13> GO (1 rows affected) (1 rows affected) 1> 2> INSERT ClassGrades VALUES(1, 1, "C+") 3> 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> 3> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter 4> FROM Classes C, ClassGrades CG 5> WHERE C.ClassID = CG.ClassID 6> GO StudentID ClassTitle GradeLetter

-------------------------------------------------- ----------- 
         1 SQL                                                C+
         2 SQL                                                A+
         2 Java                                               A

(3 rows affected) 1> 2> UPDATE Classes 3> SET ClassTitle = "The Life of Jesus Christ" 4> WHERE ClassID = 999 5> GO (1 rows affected) 1> 2> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter 3> FROM Classes C, ClassGrades CG 4> WHERE C.ClassID = CG.ClassID 5> GO StudentID ClassTitle GradeLetter

-------------------------------------------------- ----------- 
         1 SQL                                                C+
         2 SQL                                                A+
         2 The Life of Jesus Christ                           A

(3 rows affected) 1> 2> 3> drop table ClassGrades; 4> GO 1> 2> drop table Classes; 3> GO</source>


Enables the changes in the Classes table to propagate to the ClassGrades table

   <source lang="sql">

10> CREATE TABLE Classes( 11> ClassID int PRIMARY KEY, 12> ClassTitle varchar(50) 13> ) 14> GO 1> 2> CREATE TABLE ClassGrades( 3> ClassID int REFERENCES Classes(ClassID) 4> ON UPDATE CASCADE, 5> StudentID int, 6> GradeLetter varchar(2), 7> Constraint PK_ClassGrades 8> PRIMARY KEY(ClassID, StudentID) 9> ) 10> 11> INSERT Classes VALUES(1,"SQL") 12> INSERT Classes VALUES(999,"Java") 13> GO (1 rows affected) (1 rows affected) 1> 2> INSERT ClassGrades VALUES(1, 1, "C+") 3> 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> SELECT * FROM Classes 3> GO ClassID ClassTitle

-------------------------------------------------- 
         1 SQL
       999 Java

(2 rows affected) 1> 2> UPDATE Classes 3> SET ClassID = 998 4> WHERE ClassID = 999 5> GO (1 rows affected) 1> 2> SELECT * FROM Classes 3> GO ClassID ClassTitle

-------------------------------------------------- 
         1 SQL
       998 Java

(2 rows affected) 1> 2> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter 3> FROM Classes C, ClassGrades CG 4> WHERE C.ClassID = CG.ClassID 5> GO StudentID ClassTitle GradeLetter

-------------------------------------------------- ----------- 
         1 SQL                                                C+
         2 SQL                                                A+
         2 Java                                               A

(3 rows affected) 1> 2> drop table ClassGrades; 3> GO 1> 2> drop table Classes; 3> GO</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Table_Join/One_to_many&oldid=7088»