CHAPTER 4 QUERYING MULTIPLE TABLES
Making PDF-417 2d Barcode In None
Using Barcode generation for Font Control to generate, create PDF417 image in Font applications.www.OnBarcode.com
UPC - 13 Creator In None
Using Barcode maker for Font Control to generate, create GTIN - 13 image in Font applications.www.OnBarcode.com
Figure 4-3 displays a portion of the results after scrolling down more than 3,000 rows. When comparing the results to those in Figure 4-2, you will see that the rows from Sales.SalesOrderHeader join inappropriate rows from Sales.SalesOrderDetail. Both sets of results show SalesOrderID 43659, but the results are correct only in Figure 4-2. Because 1=1 is always true, every row from the first table joins every row from the second table to produce these incorrect results, which is also called a Cartesian product.
Printing Data Matrix In None
Using Barcode generator for Font Control to generate, create Data Matrix ECC200 image in Font applications.www.OnBarcode.com
Generating Barcode In None
Using Barcode generation for Font Control to generate, create Barcode image in Font applications.www.OnBarcode.com
Figure 4-3. The partial results of an incorrect join Whenever you write a query with INNER JOIN, make sure you understand the relationship between the two tables. For example, you could join the OrderQty column from the Sales. SalesOrderDetail table to the SalesOrderID column in the Sales.SalesOrderHeader table. The query would run, but the results would not make any sense at all.
PDF-417 2d Barcode Creator In None
Using Barcode creator for Font Control to generate, create PDF417 image in Font applications.www.OnBarcode.com
QR-Code Printer In None
Using Barcode generation for Font Control to generate, create Denso QR Bar Code image in Font applications.www.OnBarcode.com
Joining on a Different Column Name
UPC Symbol Encoder In None
Using Barcode maker for Font Control to generate, create UPC-A image in Font applications.www.OnBarcode.com
Generate ISSN - 10 In None
Using Barcode generator for Font Control to generate, create ISSN - 10 image in Font applications.www.OnBarcode.com
In the previous two examples, the key column names happen to be the same, but this is not a requirement. The Person.Person table contains information about people from several tables in the AdventureWorks2008 database. Figure 4-4 shows how the Person.Person and the Sales.Customer table connect. The PersonID from the Sales.Customer table joins to the BusinessEntityID in the Person.Person table. The PersonID column in the Sales.Customer table is the foreign key.
PDF 417 Encoder In Java
Using Barcode generator for Android Control to generate, create PDF417 image in Android applications.www.OnBarcode.com
Generate PDF-417 2d Barcode In Visual C#.NET
Using Barcode generation for Visual Studio .NET Control to generate, create PDF-417 2d barcode image in Visual Studio .NET applications.www.OnBarcode.com
Figure 4-4. How to connect the Sales.Customer and Person.Person tables Listing 4-3 shows an example that joins these two tables.
Making Barcode In C#.NET
Using Barcode generator for VS .NET Control to generate, create Barcode image in Visual Studio .NET applications.www.OnBarcode.com
ANSI/AIM Code 128 Generator In .NET
Using Barcode maker for Reporting Service Control to generate, create Code 128A image in Reporting Service applications.www.OnBarcode.com
CHAPTER 4 QUERYING MULTIPLE TABLES
Matrix Drawer In Java
Using Barcode encoder for Java Control to generate, create Matrix Barcode image in Java applications.www.OnBarcode.com
Barcode Drawer In None
Using Barcode maker for Software Control to generate, create Barcode image in Software applications.www.OnBarcode.com
Listing 4-3. Joining Two Tables with Different Column Names USE AdventureWorks2008; GO SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName FROM Sales.Customer AS C INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID; Figure 4-5 shows the partial results. The Person.Person table contains information about people from several tables in the database. In this case, the columns joining the two tables have different names. The PersonID from the Sales.Customer table joins to the BusinesssEntityID in the Person.Person table. This works even though the columns have different names.
Barcode Reader In .NET Framework
Using Barcode reader for .NET Control to read, scan read, scan image in VS .NET applications.www.OnBarcode.com
EAN13 Drawer In Java
Using Barcode generator for Eclipse BIRT Control to generate, create EAN13 image in BIRT reports applications.www.OnBarcode.com
Figure 4-5. The partial results of joining tables with different key column names
Encoding QR In .NET
Using Barcode drawer for Reporting Service Control to generate, create QR Code 2d barcode image in Reporting Service applications.www.OnBarcode.com
GTIN - 12 Maker In None
Using Barcode printer for Online Control to generate, create UPC-A image in Online applications.www.OnBarcode.com
Joining on More Than One Column
Encoding Barcode In None
Using Barcode drawer for Excel Control to generate, create Barcode image in Excel applications.www.OnBarcode.com
Generate Code 128 Code Set B In Java
Using Barcode creator for Java Control to generate, create Code 128C image in Java applications.www.OnBarcode.com
Although a join frequently involves joining a column from one table to a column from another table, sometimes you must join multiple columns. The AdventureWorks2008 database contains only one example in which multiple columns must be used in a single join: Sales.SalesOrderDetail to Sales.SpecialOfferProduct. Figure 4-6 shows how these two tables connect.
Figure 4-6. How to connect the Sales.SalesOrderDetail table to the Sales.SpecialOfferProduct table The Sales.SalesSpecialOfferProduct table has a composite primary key composed of SpecialOfferID plus ProductID. To identify a row in this table, you must use both columns. When joining Sales.SalesOrderDetail to the Sales.SpecialOfferProduct table, you specify both columns in the join. Here is the syntax for joining on more than one column:
CHAPTER 4 QUERYING MULTIPLE TABLES
SELECT <SELECT list> FROM <table1> [INNER] JOIN <table2> ON <table1>.<col1> = <table2><col2> AND <table1>.<col3> = <table2>.<col4> Type in and execute the code in Listing 4-4 to learn how to join on two columns. Listing 4-4. Joining on Two Columns USE AdventureWorks2008; GO SELECT sod.SalesOrderID, sod.SalesOrderDetailID, so.ProductID, so.SpecialOfferID,so.ModifiedDate FROM Sales.SalesOrderDetail AS sod INNER JOIN Sales.SpecialOfferProduct AS so ON so.ProductID = sod.ProductID AND so.SpecialOfferID = sod.SpecialOfferID WHERE sod.SalesOrderID IN (51116,51112); Take a look at the results (see Figure 4-7). Two columns, ProductID and SpecialOfferID, comprise the join condition. To determine which row matches the rows from Sales.SalesOrderDetail, both columns are used in the join condition. If the join contained only one of the columns, the results would be similar to the incorrect results in the section Avoiding an Incorrect Join. If the join contained only the ProductID, the results would show every possible SpecialOfferID row for each ProductID, not just the correct rows. Try modifying the join yourself by leaving out one of the conditions to see what happens.