QUERYING MULTIPLE TABLES in Font

Maker PDF-417 2d barcode in Font QUERYING MULTIPLE TABLES

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.

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.

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.

Figure 4-4. How to connect the Sales.Customer and Person.Person tables Listing 4-3 shows an example that joins these two tables.

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.

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:

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.