WEBVTT Kind: captions Language: en 00:00:12.060 --> 00:00:16.550 We've already talked about how to create a simple query that only uses one table. But 00:00:16.550 --> 00:00:20.220 queries that involve more than one table let you ask much more interesting questions to 00:00:20.220 --> 00:00:24.980 your database. Now this also takes a little bit more planning than a single-table query, 00:00:24.980 --> 00:00:28.310 and there are four steps that you can use to help you design it. 00:00:28.310 --> 00:00:33.290 The first step is Pinpoint exactly what you want to find out. In other words, which question 00:00:33.290 --> 00:00:37.829 are you trying to answer? In this example, let's say our bakery is sending out coupons 00:00:37.829 --> 00:00:41.800 to our customers who live outside the city limits, to entice them to come back to our 00:00:41.800 --> 00:00:46.620 bakery. Obviously, we don't want to send them to people who live really far away - we just 00:00:46.620 --> 00:00:51.050 want to focus on people who live relatively close to Raleigh. And we're also just going 00:00:51.050 --> 00:00:55.120 to send them to customers who have previously placed orders at our bakery. 00:00:55.120 --> 00:00:59.750 The customers who meet all three requirements will receive coupons. So the question that 00:00:59.750 --> 00:01:05.439 we're trying to answer is 'Which customers live in our area, are outside the city limits, 00:01:05.439 --> 00:01:08.130 and have placed an order at our bakery?' 00:01:08.130 --> 00:01:13.220 The second step is to Identify the information that we need. We'll need the customers' names 00:01:13.220 --> 00:01:17.200 and their contact information. And in order to know whether they have placed an order 00:01:17.200 --> 00:01:21.259 at our bakery, we'll also need to look at the Order ID numbers. 00:01:21.259 --> 00:01:26.380 The third step is to Locate the tables that contain the information that we need. In this 00:01:26.380 --> 00:01:31.590 case, the customers' names and contact information are stored in the Customers table. And the 00:01:31.590 --> 00:01:35.499 Order ID numbers are stored in the Orders table. So that means we need both of these 00:01:35.499 --> 00:01:38.079 tables in our query. 00:01:38.079 --> 00:01:42.889 At this point, we have enough information to start creating our query. Go to the Create 00:01:42.889 --> 00:01:49.689 tab, and select Query design. We're going to add the Customers table and the Orders 00:01:49.689 --> 00:01:56.689 table. And then close this window. And you resize these if you need to. And we're going 00:02:01.069 --> 00:02:06.139 to double click the fields that we need. So we'll add the customer's first name, last 00:02:06.139 --> 00:02:13.139 name name, street address, city, state, zip code, and phone number. And from the Orders 00:02:14.560 --> 00:02:17.880 table, we need the ID field. 00:02:17.880 --> 00:02:21.819 When you have more than one table in a query, they will be connected by a line, and this 00:02:21.819 --> 00:02:27.080 is called a Join. The join will often have an arrow that points to the left or the right, 00:02:27.080 --> 00:02:30.939 which tells the query which table to look at first. Therefore, sometimes you'll need 00:02:30.939 --> 00:02:35.709 to change the direction to get the results that you want. We're going to double-click 00:02:35.709 --> 00:02:40.950 on the join to change it, and we want to select the third option which says 'Include ALL records 00:02:40.950 --> 00:02:44.709 from the Orders Table'. And in the next video, we're going to talk a little more about why 00:02:44.709 --> 00:02:48.540 we're choosing this option, but basically this means that it will pull from the Orders 00:02:48.540 --> 00:02:54.379 table first, which ensures that only the customers who have placed an order will be included. 00:02:54.379 --> 00:02:58.939 When you click OK, you can see that the arrow now points to the left. 00:02:58.939 --> 00:03:03.640 The fourth step is to determine which search criteria you need to use. We're going to be 00:03:03.640 --> 00:03:09.409 adding criteria under the City and Phone Number fields. First, we want to exclude all of the 00:03:09.409 --> 00:03:14.689 customers who are in Raleigh. To do this, we're going to need to use a very specific 00:03:14.689 --> 00:03:21.299 syntax. So for the City criteria, type Not In, and then in parentheses type Raleigh in 00:03:21.299 --> 00:03:27.450 quotation marks. You can use this syntax whenever you want to exclude something from the query 00:03:27.450 --> 00:03:29.019 results. 00:03:29.019 --> 00:03:33.650 Now we also need some way of limiting the results to just the nearby towns. And in this 00:03:33.650 --> 00:03:38.299 case we're going to do this by getting the area code from the Phone Number field. The 00:03:38.299 --> 00:03:43.040 919 area code covers Raleigh and a number of nearby cities and towns, so this should 00:03:43.040 --> 00:03:45.230 give us a pretty good range. 00:03:45.230 --> 00:03:51.109 We'll need to use a syntax that looks at the beginning of each phone number. So type Like, 00:03:51.109 --> 00:03:58.109 and in parentheses, type quote 919, asterisk, end quote. The asterisk means that any phone 00:03:58.769 --> 00:04:02.159 number can come after the 919 area code. 00:04:02.159 --> 00:04:05.549 And there are many other syntaxes that you can use, and in the next video we'll look 00:04:05.549 --> 00:04:08.219 at a few other examples of these. 00:04:08.219 --> 00:04:12.420 Now in this case we're putting the criteria on the same row, because we want the customers 00:04:12.420 --> 00:04:16.620 to meet both of these criteria. If they just meet one of the criteria, then they're not 00:04:16.620 --> 00:04:21.310 going to be included in the query results. If we want them to meet one or the other, 00:04:21.310 --> 00:04:25.240 then we would one of these criteria on the next row. 00:04:25.240 --> 00:04:31.430 This query is finished now, so we can Run it to see the results. And you can see that 00:04:31.430 --> 00:04:35.860 each customer meets both criteria. They are not from Raleigh, and their phone numbers 00:04:35.860 --> 00:04:38.430 begin with 919. 00:04:38.430 --> 00:04:43.100 So generally, more complex queries require more planning. But you can make it a lot easier 00:04:43.100 --> 00:04:48.810 by just following the four steps of Pinpointing exactly what you want to find out, Identifying 00:04:48.810 --> 00:04:54.280 the information that you need, Locating the tables that contain the information, and Determining 00:04:54.280 --> 00:04:59.819 exactly what criteria you need. And in the next video, we're going to look at joins and 00:04:59.819 --> 00:05:06.819 search criteria in a little bit more detail.