MCITP SQL Server 2005 Database Developer All-in-One Exam Guide in Visual Studio .NET

Creation QR Code 2d barcode in Visual Studio .NET MCITP SQL Server 2005 Database Developer All-in-One Exam Guide

Notice that the FROM statement is modified slightly by adding the INNER JOIN and the second table name Next, we must identify the fields in each of the tables that hold the columns that define the relationship (ProductModelID in both tables in this example) with the ON clause TIP INNER JOIN is frequently shortened to just JOINYou will see the two syntax choices used interchangeably, but most often, you ll just see JOIN A partial output of the previous script is shown in Figure 4-5

You can see that the JOIN statement identifies the first table (ProductionProduct) and the second table (ProductionProductModel) In addition, it identifies the PK and FK that will be used to join the tables in the ON clause (ProductModelID in each table) We don t have to include the PK or FK in our select list Often, this is meaningless data to end users Instead, we just include what we want in the output Notice that I used an alias in the select list for the Model Name This is because we have two columns titled Name, one in each table To differientiate between the two in the output, I used an alias of Model Name for the second Since it has a space, I delimited it with single quotes Let s take a query that we used earlier to pull the top five sales out of the database While it lists the CustomerID and SalesPersonID, you will very likely be requested to add actual names The following query shows where we start:

Next, we ll modify the query to pull the data but also list the actual salesperson names To help conceptualize the tables that are joined, let s look at the database diagram that shows the tables that hold the data we want Figure 4-6 shows the relationships among the three tables Notice that in the diagram the schema is shown in parentheses NOTE Notice in the diagram that the object and schema names are swapped from how they would appear in the four-part naming convention For example, in the diagram the Contact table is listed as Contact(Person), but using the four-part name it would be listed as ServerAdventureWorksPersonContact, or shortened to PersonContact

Figure 4-6 A database diagram of the SalesSalesOrderHeader, SalesSalesPerson, and PersonContact tables

If you ve used Microsoft Access, you may have used the Query By Example (QBE) window SQL Server has a similar tool called the View Designer in SSMS that is used to create views We used it in 2, and we ll use it again here to show how to easily build our SELECT statement joining multiple tables In the following exercise, we ll use the View Designer to create a listing of customers who have ordered products, including how much the orders were Exercise 47: Use the View Designer to Create Complex Joins 1 Open SSMS and then open the AdventureWorks database by double-clicking Databases and double-clicking the AdventureWorks database 2 Right-click Views and select New View 3 In the Add Table dialog box, choose the Contact (Person) table and click Add Select the SalesOrderHeader (Sales) table and click Add Click Close 4 Arrange the tables so they look similar to Figure 4-7 Notice that the relationship is shown and points to the ContactID column in both tables

5 Now we ll select the columns we re interested in It s important to select them in the order we want them to appear in the select list a In the Contact (Person) table, click the LastName and FirstName columns b In the SalesOrderHeader (Sales) table, click the CustomerID and TotalDue columns Notice that two things have occurred as you ve selected columns:

The columns are added into the column list pane in the center The SELECT statement is being built in the lower pane TIP The View Designer has four panes The top pane with the tables added is called the Diagram pane The next pane with the selected columns listed is the Criteria pane The third pane where the SELECT statement is being built is the SQL pane Lastly, the Results pane displays your result set when the query is executed

6 Add aliases to your column list: a In the Alias column next to LastName, type in Last Name and press ENTER Notice that it adds brackets around Last Name since there is a space Observe how the SELECT statement changes b In the Alias column next to FirstName, enter First Name c In the Alias column next to CustomerID, enter Customer Number d In the Alias column next to TotalDue, enter Order total Your display should look similar to Figure 4-8 7 Test this SELECT statement using two methods First, run it in the View Designer GUI by clicking the red exclamation mark (the Execute SQL button) on the menu bar, as shown in Figure 4-9 8 We can also copy the query and paste it into any application where we want to run the query: a Click in the pane holding the SELECT statement, and press CTRL+A to select it all b Press CTRL+C to copy the SELECT statement to the clipboard