WEBVTT 00:02.070 --> 00:07.560 So I've reset my database and I've made two sales, and as you can see, I have an order ID one, an 00:07.560 --> 00:15.090 order ID two, and those correspond to these customers, Bruce Wayne and Clark Kent and these transactions. 00:15.100 --> 00:16.960 So I have some data I can actually query. 00:17.790 --> 00:22.680 Now, as you might have noticed, our information is actually stored in the orders table, the customer's 00:22.680 --> 00:24.560 table and the transaction table. 00:24.990 --> 00:28.850 So our query is going to affect or use all three tables. 00:28.860 --> 00:34.260 So let's just open a query window right in the database and see if we can get some information in a 00:34.260 --> 00:35.070 useful format. 00:35.940 --> 00:38.820 So let's give ourselves some room and I'll start writing a query. 00:38.830 --> 00:42.630 So I'll write select because this is a select and I want to select. 00:42.870 --> 00:47.400 I definitely want the order ID and I'm going to prefix it with Oh, and I'll use that as an alias to 00:47.400 --> 00:47.900 the table. 00:47.910 --> 01:01.650 So I want odah id or dot widget id dot transaction idy modot customer I.D. and then I'll put this on 01:01.650 --> 01:02.280 the next line. 01:03.510 --> 01:13.980 Odon status idy oduor quantity as well, it looks good, oh, the amount, the amount of the transaction. 01:17.720 --> 01:25.730 And I'll get the O'Dowd created up and the updated eye and I'll just finish the query right now to make 01:25.730 --> 01:28.940 sure that works from orders. 01:29.020 --> 01:38.540 Oh, and I'll say order by product created at descending to get the most recent ones. 01:38.540 --> 01:40.180 First, let's make sure that query works. 01:40.550 --> 01:41.750 So I'll run the query. 01:42.530 --> 01:43.970 And there I have that information. 01:45.010 --> 01:46.790 Now, I want more than just the order. 01:46.960 --> 01:53.050 I also want the widgets and what the name of what they sold down here in my from I'll say left join 01:53.680 --> 01:58.450 widgets w on and I'll join them on the obvious things. 01:59.530 --> 02:09.700 Widget I.B. widget in the orders table is equal to me and I will add up here after created out the idea 02:09.700 --> 02:13.690 of the widget so it doesn't name. 02:14.830 --> 02:19.240 And that should be enough from the witness table, so let's get rid of the question of the comma and 02:19.240 --> 02:20.240 run this query again. 02:20.260 --> 02:24.700 And now I should have the widget name and the ID and I do OK. 02:25.510 --> 02:27.220 So I definitely want the widgets. 02:27.460 --> 02:29.320 I also want the transactions. 02:29.350 --> 02:33.880 So left join transactions with the alias aliased on. 02:33.910 --> 02:41.520 And once again, that transaction, aside from the orders table, equals t ID. 02:42.970 --> 02:45.280 And then let's put the fuels we want from the transaction. 02:46.450 --> 02:52.210 So we want the ID to ID, we already have that, but I'll get it in the amount which we already have, 02:52.390 --> 02:53.170 but I'll get it again. 02:54.560 --> 03:01.820 The currency, and we want the last four digits of the credit card, Ketut, last dash for. 03:03.150 --> 03:07.200 And the expiry month and the expiry year. 03:11.310 --> 03:12.680 The payment intented. 03:17.540 --> 03:19.190 And the banker return code. 03:21.430 --> 03:25.270 We're not going to use most of these, but we might at some point in the future, so we will grab it 03:25.810 --> 03:27.800 and let's just run that and make sure that works. 03:27.820 --> 03:29.740 Run current and there it is. 03:29.890 --> 03:31.660 I have all the information, which is good. 03:32.030 --> 03:35.990 OK, and the last bit of information we want is the customers. 03:36.040 --> 03:46.870 Who is this sale for so left join customers si on and again, customer, the customer is in the orders 03:46.870 --> 03:57.260 table and that's going to be equal to set up ID and we'll grab here just the ID the customer and see 03:57.400 --> 04:05.470 the first name, see the last name and their email address, see the email and I should be able to run 04:05.470 --> 04:08.380 this now run current and get that. 04:08.560 --> 04:14.170 Now this query looks good, but of course it's actually going to get everything in the orders table 04:14.290 --> 04:19.680 and we only want the ones that are actual orders for a single digit or sales for a single digit. 04:20.320 --> 04:29.280 So let's put it where clause in here where it is recurring equals zero. 04:30.340 --> 04:33.970 So that will get me the sales for any product, any widget. 04:34.000 --> 04:36.520 That is not a recurring sale and I've run it again. 04:36.520 --> 04:40.720 I should get exactly the same thing because I have no other sales in my database right now. 04:40.750 --> 04:42.940 Run current and it works great. 04:43.420 --> 04:44.560 So this will be a query. 04:44.590 --> 04:49.900 So in the next lecture we'll use this query to build the necessary database function that we can call 04:49.900 --> 04:53.470 from the back end or call from the front end or call from wherever we need to call it. 04:53.770 --> 04:55.930 And we'll get started on that in the next lecture.