Using the GROUP BY Clause : Table Join « Table Join « SQL Server / T-SQL Tutorial






4>
5> CREATE TABLE Classification (
6>      Classif_ID         integer  NOT NULL PRIMARY KEY,
7>      Classification    varchar(25))
8> GO
1>
2> INSERT into Classification VALUES( 1,"Pop")
3> INSERT into Classification VALUES( 2,"Country")
4> INSERT into Classification VALUES( 3,"Alternative")
5> INSERT into Classification VALUES( 4,"Metal")
6> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> CREATE TABLE CD (
4>      CD_ID              integer  NOT NULL PRIMARY KEY,
5>      CD_Title           varchar(40),
6>      Composer_ID        integer  NOT NULL,
7>      Classif_ID         integer  NOT NULL,
8>      SalesPrice        money,
9>      AverageCost       money)
10> GO
1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)
2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
32> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> SELECT CD.Classif_ID,Classification.Classification,
3>          Count(CD.CD_ID) "Total Offerings"
4>     FROM CD,Classification
5>          WHERE CD.Classif_ID = Classification.Classif_ID
6>               GROUP BY CD.Classif_ID,Classification.Classification
7> GO
Classif_ID   Classification            Total Offerings
----------- ------------------------- ---------------
          1 Pop                                    19
          2 Country                                 2
          3 Alternative                             3
          4 Metal                                   7

(4 rows affected)
1>
2>
3> drop table Classification;
4> drop table CD;
5> GO








4.1.Table Join
4.1.1.SQL Server 2005 join types fall into three categories: inner, outer, and cross.
4.1.2.Selecting authors and titles using only joins.
4.1.3.A SELECT statement that joins the Bankers and Billings tables
4.1.4.Joining Tables in the WHERE Clause (not ANSI standard)
4.1.5.Using the GROUP BY Clause
4.1.6.Joining three tables.
4.1.7.Joins and Subqueries
4.1.8.Left and Right Outer Joins
4.1.9.Joining Tables in the FROM Clause (ANSI standard)
4.1.10.Table Aliasing
4.1.11.Join tables with two columns
4.1.12.The result of the previous join is then joined to another table
4.1.13.SQL-92 Three-Way Inner Joins
4.1.14.Forcing the Order of Join Processing