1 00:00:02,400 --> 00:00:04,466 - [Maximilian] So let's now apply what we learned 2 00:00:04,466 --> 00:00:06,333 and let's write a brand new query. 3 00:00:07,333 --> 00:00:12,333 We wanna select all our reviews from the reviews table, 4 00:00:12,866 --> 00:00:17,166 where the rating is actually greater than three. 5 00:00:18,500 --> 00:00:20,533 So where rating is greater than three 6 00:00:21,400 --> 00:00:24,200 now if execute this, I get all these ratings 7 00:00:24,200 --> 00:00:27,600 but now I wanna merge in the related to restaurant data, 8 00:00:27,600 --> 00:00:30,533 and for this, we got this restaurant ID field, of course. 9 00:00:31,400 --> 00:00:33,700 So therefore we need an INNER JOIN. 10 00:00:33,700 --> 00:00:38,400 So before we add where we can add INNER JOIN 11 00:00:38,400 --> 00:00:42,966 and we can join our restaurants in this case here 12 00:00:42,966 --> 00:00:47,966 to our reviews on reviews dot restaurant underscore ID 13 00:00:48,900 --> 00:00:52,200 being equal to restaurants.id. 14 00:00:54,300 --> 00:00:57,333 With that, we tell SQL that we wanna merge 15 00:00:57,333 --> 00:01:01,833 in our restaurants data matching on that restaurant ID field 16 00:01:01,833 --> 00:01:06,066 and the ID primary key in the restaurants table. 17 00:01:06,066 --> 00:01:09,166 And we still filter for the rating greater than three. 18 00:01:10,266 --> 00:01:13,933 If I execute this, I got this result here, 19 00:01:13,933 --> 00:01:15,233 which isn't too bad. 20 00:01:15,233 --> 00:01:17,466 Now we have our restaurant data here, 21 00:01:19,133 --> 00:01:23,100 but of course the restaurant data also does have some data 22 00:01:23,100 --> 00:01:26,266 that can be merged in the address and the type. 23 00:01:27,333 --> 00:01:31,433 So of course we can add more INNER JOINs here. 24 00:01:31,433 --> 00:01:35,700 We cannot also join on the addresses 25 00:01:35,700 --> 00:01:38,700 and we wanna make sure that we merge in the addresses, 26 00:01:38,700 --> 00:01:43,433 matching on our restaurants dot address underscore ID 27 00:01:43,433 --> 00:01:47,400 being equal to address dot ID. 28 00:01:47,400 --> 00:01:49,966 So now we're joining on another table 29 00:01:49,966 --> 00:01:52,866 that's brought in by our first join, 30 00:01:52,866 --> 00:01:54,900 we are joining on our restaurants table 31 00:01:54,900 --> 00:01:56,833 and the addresses table. 32 00:01:56,833 --> 00:01:59,400 And that's also something we can do. 33 00:01:59,400 --> 00:02:03,166 If I execute this, we got that address data in here. 34 00:02:04,433 --> 00:02:06,966 Hence we can of course do the same for our types. 35 00:02:06,966 --> 00:02:09,466 We can add another INNER JOIN 36 00:02:09,466 --> 00:02:13,133 and showing the types on restaurants, whoops, 37 00:02:13,133 --> 00:02:16,400 restaurants dot type underscore ID 38 00:02:16,400 --> 00:02:18,633 being equal to types dot ID. 39 00:02:19,866 --> 00:02:23,833 So what we did before as well now here in this big select 40 00:02:23,833 --> 00:02:26,900 and join statement on our reviews. 41 00:02:28,566 --> 00:02:30,600 And this now also works 42 00:02:32,700 --> 00:02:34,800 now to clean up that output a little bit, 43 00:02:34,800 --> 00:02:37,033 I don't wanna select everything though. 44 00:02:38,100 --> 00:02:40,166 Instead here from my reviews, 45 00:02:40,166 --> 00:02:43,033 I do wanna get everything let's say, 46 00:02:44,400 --> 00:02:46,533 but for my restaurants, 47 00:02:46,533 --> 00:02:50,400 I only wanna get the name as restaurant name 48 00:02:52,133 --> 00:02:55,000 to make it clear that this is the restaurant name, 49 00:02:55,000 --> 00:02:57,500 to give this column a clearer label. 50 00:02:59,100 --> 00:03:01,133 I don't care about the restaurant ID 51 00:03:01,133 --> 00:03:04,466 and I don't wanna get the address ID or type ID here 52 00:03:04,466 --> 00:03:07,833 because I already got all that joined detailed data. 53 00:03:09,133 --> 00:03:13,833 Now for the address, I wanna get everything 54 00:03:14,733 --> 00:03:17,366 and for the type, 55 00:03:17,366 --> 00:03:21,800 I only wanna get the name as restaurant, 56 00:03:21,800 --> 00:03:24,833 whoops restaurant under score type, maybe. 57 00:03:24,833 --> 00:03:26,966 So give that an extra label. 58 00:03:28,766 --> 00:03:32,166 And if we now execute this, that's the results at now 59 00:03:32,166 --> 00:03:35,533 and that's a bit more readable, I would argue. 60 00:03:35,533 --> 00:03:37,966 We could remove more columns. 61 00:03:37,966 --> 00:03:41,933 We could fine-tune this select statement even more 62 00:03:41,933 --> 00:03:43,233 but with that, 63 00:03:43,233 --> 00:03:47,400 you already see the most important thing in action already, 64 00:03:47,400 --> 00:03:51,300 how you can write more complex select statements 65 00:03:51,300 --> 00:03:54,666 where you control which kind of data you wanna select, 66 00:03:54,666 --> 00:03:58,100 where you have a where clause and most importantly, 67 00:03:58,100 --> 00:04:01,966 where you join in relate to data from other tables 68 00:04:01,966 --> 00:04:04,233 with that INNER JOIN statement. 69 00:04:05,133 --> 00:04:08,500 And this is super important because SQL 70 00:04:08,500 --> 00:04:11,100 and working with SQL databases 71 00:04:11,100 --> 00:04:15,800 is all about splitting your data across multiple tables 72 00:04:15,800 --> 00:04:18,632 so that the data is easier to manage. 73 00:04:18,632 --> 00:04:22,500 And you have that clear separation of data pieces, 74 00:04:22,500 --> 00:04:25,200 but you then of course also need to be able 75 00:04:25,200 --> 00:04:27,400 to merge that data together 76 00:04:27,400 --> 00:04:29,500 whenever you need to work with it 77 00:04:29,500 --> 00:04:31,500 whenever you need to query it. 78 00:04:31,500 --> 00:04:34,166 And that's what you can do with INNER JOIN 79 00:04:34,166 --> 00:04:37,166 in your select statements, as you'll learn it here. 80 00:04:38,233 --> 00:04:39,200 And therefore, 81 00:04:39,200 --> 00:04:44,133 this is now the essence of SQL and of working with SQL. 82 00:04:45,100 --> 00:04:48,300 These are features, and this is a syntax 83 00:04:48,300 --> 00:04:50,133 you will see a lot and therefore 84 00:04:50,133 --> 00:04:53,066 it belongs into any web development course 85 00:04:53,066 --> 00:04:55,700 and now you get these basics here as well.