WEBVTT Kind: captions Language: en 00:00:11.599 --> 00:00:15.509 We've already talked about how to create a simple query that only uses one table. 00:00:15.509 --> 00:00:19.680 But queries that involve more than one table let you ask much more interesting questions 00:00:19.680 --> 00:00:20.680 to your database. 00:00:20.680 --> 00:00:25.160 Now this also takes a little bit more planning than a single-table query, and there are four 00:00:25.160 --> 00:00:28.790 steps that you can use to help you design it. 00:00:28.790 --> 00:00:32.290 The first step is pinpoint exactly what you want to find out. 00:00:32.290 --> 00:00:35.670 In other words, which question are you trying to answer? 00:00:35.670 --> 00:00:39.930 In this example, let's say our bakery is sending out coupons to our customers who live outside 00:00:39.930 --> 00:00:43.219 the city limits, to entice them to come back to our bakery. 00:00:43.219 --> 00:00:47.530 Obviously, we don't want to send them to people who live really far away—we just want to 00:00:47.530 --> 00:00:50.649 focus on people who live relatively close to Raleigh. 00:00:50.649 --> 00:00:54.200 And we're also just going to send them to customers who have previously placed orders 00:00:54.200 --> 00:00:55.710 at our bakery. 00:00:55.710 --> 00:00:59.940 The customers who meet all three of these requirements will receive coupons. 00:00:59.940 --> 00:01:04.340 So the question that we're trying to answer is, "Which customers live in our area, are 00:01:04.340 --> 00:01:09.120 outside the city limits, and have placed an order at our bakery?" 00:01:09.120 --> 00:01:12.840 The second step is to identify the information that we need. 00:01:12.840 --> 00:01:16.460 We'll need the customers' names and their contact information. 00:01:16.460 --> 00:01:19.670 And in order to know whether they have placed an order at our bakery, we'll also need to 00:01:19.670 --> 00:01:22.640 look at the Order ID numbers. 00:01:22.640 --> 00:01:27.689 The third step is to locate the tables that contain the information that we need. 00:01:27.689 --> 00:01:32.450 In this case, the customers' names and contact information are stored in the Customers table. 00:01:32.450 --> 00:01:35.740 And the Order ID numbers are stored in the Orders table. 00:01:35.740 --> 00:01:41.310 So that means we need both of these tables in our query. 00:01:41.310 --> 00:01:44.890 At this point, we have enough information to start creating our query. 00:01:44.890 --> 00:01:51.570 Go to the Create tab… and select Query Design. 00:01:51.570 --> 00:01:58.740 We're going to add the Customers table and the Orders table. 00:01:58.740 --> 00:02:02.820 And then close this window. 00:02:02.820 --> 00:02:09.560 You can resize these if you need to. 00:02:09.560 --> 00:02:13.110 Then we're going to double click the fields that we need. 00:02:13.110 --> 00:02:23.830 So we'll add the customer's first name, last name, street address, city, state, zip code, 00:02:23.830 --> 00:02:25.970 and phone number. 00:02:25.970 --> 00:02:30.450 And from the Orders table, we'll need the ID field. 00:02:30.450 --> 00:02:34.390 When you have more than one table in a query, they will be connected by a line, and this 00:02:34.390 --> 00:02:36.170 is called a "join." 00:02:36.170 --> 00:02:40.170 The join will often have an arrow that points to the left or the right, which tells the 00:02:40.170 --> 00:02:43.280 query which table to look at first. 00:02:43.280 --> 00:02:47.420 Sometimes you'll need to change the direction to get the results that you want. 00:02:47.420 --> 00:02:52.170 We're going to double-click on the join to change it… and we want to select the 3rd 00:02:52.170 --> 00:02:56.540 option which says "Include ALL records from the Orders Table." 00:02:56.540 --> 00:02:59.450 And in the next video, we're going to talk a little more about why we're choosing this 00:02:59.450 --> 00:03:04.230 option, but basically this means that it will pull from the Orders table first, which ensures 00:03:04.230 --> 00:03:09.310 that only the customers who have placed an order will be included. 00:03:09.310 --> 00:03:15.280 When you click OK, you can see that the arrow now points to the left. 00:03:15.280 --> 00:03:19.730 The fourth step is to determine which search criteria you need to use. 00:03:19.730 --> 00:03:24.470 We're going to be adding criteria under the City and Phone Number fields. 00:03:24.470 --> 00:03:29.780 First, we want to exclude all of the customers who are in Raleigh. 00:03:29.780 --> 00:03:33.810 To do this, we're going to need to use a very specific syntax. 00:03:33.810 --> 00:03:39.820 So for the City criteria, type "Not In"… and then in parentheses type "Raleigh" in 00:03:39.820 --> 00:03:41.360 quotation marks. 00:03:41.360 --> 00:03:46.580 You can use this syntax whenever you want to exclude something from the query results. 00:03:46.580 --> 00:03:51.090 Now we also need some way of limiting the results to just the nearby towns. 00:03:51.090 --> 00:03:54.630 And in this case we're going to do this by getting the area code from the Phone Number 00:03:54.630 --> 00:03:56.130 field. 00:03:56.130 --> 00:04:00.990 The 919 area code covers Raleigh and a number of nearby cities and towns, so this should 00:04:00.990 --> 00:04:03.260 give us a pretty good range. 00:04:03.260 --> 00:04:07.260 We'll need to use a syntax that looks at the beginning of each phone number. 00:04:07.260 --> 00:04:16.470 So type "Like"… and in parentheses, type quote "919*" end quote. 00:04:16.470 --> 00:04:21.280 The asterisk means that any phone number can come after the 919 area code. 00:04:21.280 --> 00:04:24.660 And there are many other syntaxes that you can use, and in the next video we'll look 00:04:24.660 --> 00:04:27.470 at a few other examples of these. 00:04:27.470 --> 00:04:31.670 Now in this case we're putting the criteria on the same row, because we want the customers 00:04:31.670 --> 00:04:33.970 to meet both of these criteria. 00:04:33.970 --> 00:04:37.540 If they just meet one of the criteria, then they're not going to be included in the query 00:04:37.540 --> 00:04:38.700 results. 00:04:38.700 --> 00:04:42.530 If we needed them to meet one or the other, then we would put one of the criteria on the 00:04:42.530 --> 00:04:46.910 next row. 00:04:46.910 --> 00:04:51.740 This query is finished now, so we can run it to see the results. 00:04:51.740 --> 00:04:54.960 And you can see that each customer meets both criteria. 00:04:54.960 --> 00:05:00.060 They are not from Raleigh, and their phone numbers begin with 919. 00:05:00.060 --> 00:05:03.540 So generally, more complex queries require more planning. 00:05:03.540 --> 00:05:07.100 But you can make it a lot easier by just following the four steps of 00:05:07.100 --> 00:05:10.250 pinpointing exactly what you want to find out, 00:05:10.250 --> 00:05:15.460 identifying the information that you need, locating the tables that contain the information, 00:05:15.460 --> 00:05:18.930 and determining exactly what criteria you need. 00:05:18.930 --> 00:05:22.750 And in the next video, we're going to look at joins and search criteria in a little bit 00:05:22.750 --> 00:05:23.490 more detail.