Lecture 18-- Create a view of the data
- [Instructor] Because our data tables are related together using primary and foreign key fields, we can pull information out of multiple tables and piece it together again into a single location. This is called a view, and it's an extremely handy component to any SQL database. To demonstrate how they work, I want to first explore a couple of tables inside of the Wide World Importers database. The first table I want to find is called Sales.Invoices. Let's go ahead and expand it open, and we'll find the Columns folder and expand that open. This is a listing of all the different columns that occur inside of the Invoices table.
The InvoiceID is the primary key for the table. And we can tell that based on the icon, as well as the PK here in the description. This table also has a reference to the CustomerID that placed the order. Notice though that there isn't any column that would store address details that we would need in order to deliver the order. Let's go ahead and close up the Columns folder. And we'll expand the folder for Keys. This folder has a reference to all the different ways that this table, the Invoices table, is related to other tables in the database. One of these lines, this one here, references a different table based off of the CustomerID.
Let's go ahead and expand this open a little bit so that we see that the CustomerID is related to a table called Sales.Customers. Let's go ahead and take a look at that table. I'll go ahead and close up the Sales.Invoices table, and find Sales.Customers. I'll expand that open, and we'll expand open its Columns. Once we've identified a CustomerID from the invoices table, we can bring that over to this table. Here we have the reference to the CustomerID, as well, if I scroll down, I can see their delivery address line one and two, as well as their delivery postal code.
Now there isn't any entry for the city, but there is, up here, a CityID field. We have the DeliveryCityID, as well as the PostalCityID. In this database, cities are all linked to a master table of cities. And you can find that underneath the Application.Cities table. Let's take a look at that one. Go ahead and close this up. Scroll back up to the top, and we'll find Application.Cities. I'll expand that open and take a look at its Columns. Once we have the CityID from the Customers table, we can use that to look up the city name. Now rather than go all over the database and search through multiple tables to get all of this information, we can pull it all together into a single view object.
To create one, let's go ahead and close everything up, and we'll find the Views folder for this database. I'll right click on it and choose New View. That'll open up a new window where we get to add different tables into our view. The first table is the Invoices table. I'll go ahead and select it and press the Add button. Then I also want to add in information from my Customers table, so I'll go ahead and select that and press Add. And finally, I want to add in the information from my Cities table. Let's go ahead and select that and press Add.
Those are the three tables that I want to add into my view, so I'll go ahead and close the Add Table window. The view designer screen includes four different panes. At the very top we have the diagram, then in this section we have the criteria that we'll select. Next we have a window that displays the transact SQL syntax of our view, and finally at the very bottom we have the results. Now I only want to focus on the first two, the design diagram up here, as well as the criteria. And we can toggle off the other two by coming up here to our toolbar and finding those buttons. Now my monitor is a little bit small here, so I can see the Show Diagram pane toggle button is right there.
If I use the dropdown menu, though, I can see the other three, Show Criteria, Show SQL Pane and Show Results. Let's go ahead and turn off these last two. That'll leave us with a diagram and the criteria. Now these three windows up here at the top represents the three different tables that we added into our view, the Invoices, Customers and Cities. We can rearrange these a little bit to make everything easier to see on the screen. Let's go ahead and just drag on these edges a little bit. And we'll put these into a new position.
Now these lines that join each of these tables together identify the different relationships between tables. So for instance, between the Invoices table and the Customers table, I have two relationships that join the CustomerID to this CustomerID over here, as well as the BillToCustomerID to the same CustomerID column in the Customers table. Right now I'm not interested in any billing information, I just want the customer that placed the invoice order. So let's go ahead and remove the join between BillToCustomerID and CustomerID. I can do that by selecting it, then right clicking on it and choosing Remove. Next we'll do the same thing between the Customers table the the Cities table.
We have two different relationships between the delivery city as well as postal city. I'm only interested in the delivery city. Let's go ahead and remove the other join by selecting it, right clicking on it, and choosing Remove. Now we can start adding columns from these tables that we want to see in our view. To do that I'll simply place checkmarks next to the columns I want to see. From the Invoices table, I want to see the InvoiceID, the CustomerID and the InvoiceDate. From the Customers table, I want to see the CustomerName, and I'll scroll down, I want the DeliveryAddressLine1, DeliveryAddressLine2 and the DeliveryPostalCode.
And finally, from the Cities table, I just want to see the CityName. That'll put all of those different columns down here into our view. Let's go ahead and expand this up, and we can see all of them listed right there. Next let's take a look at the SQL syntax that has been generated here. I'll go back and toggle on the Show SQL pane. And we can see that the Select statement and From statements are written out right there. This is very similar to the Select statements that we saw earlier in the chapter. We're selecting a number of columns here from the tables here. Notice that these tables are joined together, and it specifies how the tables are joined, based off of which colmuns and values.
Let's go ahead and toggle that off and toggle on the results. And we don't see any results automatically, we have to press this button here that says Execute SQL. I'll press that Play button and we'll start to see the results being generated down below. And now we can see that it's generating a table that includes all those columns, InvoiceID, CustomerID, InvoiceDate, the CustomerName, delivery addresses and the postal code, coming from the Customers table. And finally, if I scroll to the right, we see the CityName that's coming out of the Cities table. Let's come up to our toolbar and press the Save icon, and we'll call this view OrderDetails.
Then I can go ahead and close this window, and we'll take a look inside of our Views folder. Now I'm not seeing anything here. Let's go ahead and right click on it and choose Refresh. And we should see the new view that we just created right there. Now we can make use of it by executing queries against the view, just like we would execute a query against a table. I'll come up and I'll press the New Query button, and we'll just select everything out of the OrderDetails view. I'll do this by typing in Select star to get all of the columns, From, and then where I want this to come from.
In this case, not a table but the View called OrderDetails. When I execute this, we'll get the results down below that we expected. Now any time that I want to see the name of a customer that's attached to an invoice, as well as the full delivery address, including the city, I can just open up this view, rather than digging through all three of those tables individually. This view combines all of the records together that I need from across the three related tables into one single location that's easy to access.
▶️ DTube
▶️ IPFS