1 00:01.320 --> 00:02.360 Hello everyone. 2 00:02.920 --> 00:04.040 Good morning all. 3 00:05.200 --> 00:07.040 So happy Ganesh Chaturthi. 4 00:08.360 --> 00:16.200 And ah, let's have the continuation of our Fang and max SQL queries practice. 5 00:17.480 --> 00:20.440 So they have given us the friendship table to us. 6 00:22.680 --> 00:27.720 And in the friendship table, they have given us the user one id and user two id. 7 00:28.360 --> 00:32.280 So user one id and user two id is the primary key means. 8 00:32.280 --> 00:37.840 The combination of these two columns is the primary key for this table, and each row of this table 9 00:37.840 --> 00:46.000 indicates that there is a friendship relation between user one ID and user two id and there is a like 10 00:46.000 --> 00:46.520 table. 11 00:46.520 --> 00:55.520 So in the like table, user id column is given in page id column is given, and user id and page id 12 00:55.520 --> 00:57.600 is the primary key for this table. 13 00:58.440 --> 01:08.720 Each row of this table indicates that user ID likes page ID, so write a solution to recommend pages 14 01:08.720 --> 01:16.880 to the user with user ID equal to one using the pages that your friends liked. 15 01:17.760 --> 01:21.560 So it should not recommend pages you already liked. 16 01:22.080 --> 01:27.040 So user ID one has some friends, right? 17 01:27.680 --> 01:42.000 So we need to refer those pages to the user id one which pages are liked by their friend and if that 18 01:42.000 --> 01:49.800 user id one if that page is already liked by the user id one, then we need to exclude that pages, 19 01:51.680 --> 01:55.560 so it should not recommend pages you already liked, right? 20 01:56.080 --> 01:59.320 Return the result table in any order without duplicates. 21 02:00.800 --> 02:02.480 So here is the example. 22 02:04.520 --> 02:05.240 Input table. 23 02:05.240 --> 02:06.760 This is a friendship table. 24 02:06.800 --> 02:07.880 User ID one. 25 02:07.920 --> 02:08.960 User id two. 26 02:11.650 --> 02:13.370 User two underscore ID right. 27 02:14.210 --> 02:20.530 So now if you see over here user one has this much of friends right. 28 02:20.570 --> 02:24.770 User ID two then user ID three. 29 02:26.250 --> 02:30.250 Then user ID four and then user ID six. 30 02:30.250 --> 02:40.130 Because ultimately we have to check one in either user one ID or user two ID, right? 31 02:40.970 --> 02:45.970 So six is also a friend with user one. 32 02:46.170 --> 02:47.690 Here you can see that four. 33 02:50.530 --> 02:51.690 Three and two. 34 02:53.770 --> 02:54.130 Right. 35 02:54.170 --> 03:00.770 Now we need to check what are the pages has been liked by two, three, four and six. 36 03:01.690 --> 03:13.210 So if you see over here two has liked 23 page ID 1023 is like 24 page ID and four is like 56. 37 03:14.410 --> 03:19.050 So now we need to identify all the page liked by their friend. 38 03:20.530 --> 03:30.650 And then we need to check that user one has which page is liked already that we need to exclude. 39 03:31.850 --> 03:32.650 So here. 40 03:35.330 --> 03:42.770 We need to I have already created both the tables friendship table and likes table and inserted the 41 03:42.770 --> 03:43.930 data into the same. 42 03:45.330 --> 03:46.730 And now if you see. 43 03:49.930 --> 03:54.810 Now how we can approach to resolve this query. 44 03:54.810 --> 03:55.250 Right. 45 03:55.650 --> 03:57.290 So here I have. 46 04:00.730 --> 04:01.850 I have written the. 47 04:06.010 --> 04:17.130 Query like the user ID one should become either in user one ID or it should become in either user two 48 04:17.130 --> 04:20.850 to ID, so we need to check that data. 49 04:21.650 --> 04:25.530 So let me select the. 50 04:28.890 --> 04:29.530 Data. 51 04:29.570 --> 04:33.130 And now we are just. 52 04:36.570 --> 04:39.970 Referring user one ID right. 53 04:40.090 --> 04:45.170 So this 234 and six are the friends with user one one right. 54 04:45.690 --> 04:47.450 Now what is our requirement. 55 04:48.810 --> 04:55.770 Well now we need to check which are all the pages has been liked by two, three, four and six. 56 04:56.410 --> 05:06.930 So all these 23, 24, 56, 32, 77 and 88, these all pages are are liked by the user ones. 57 05:06.930 --> 05:11.250 Friend user one user ID one friend friends. 58 05:11.250 --> 05:11.690 Right. 59 05:12.290 --> 05:20.530 Now we need to check which are the pages has been liked by user one already. 60 05:20.530 --> 05:26.460 So 88 page ID 88 is already liked by user one, right? 61 05:26.740 --> 05:30.940 So now we need to exclude that page from our result set. 62 05:31.300 --> 05:34.820 So what we can do we can write the accept statement. 63 05:35.260 --> 05:40.460 And then that accept statement from the first block. 64 05:40.460 --> 05:44.500 We need to accept that already liked by the user ID one. 65 05:44.940 --> 05:46.500 That is 88 right. 66 05:46.860 --> 05:50.780 So now this should be our output 23 2456. 67 05:55.020 --> 05:56.660 33 and 77. 68 05:56.660 --> 05:57.060 Right. 69 05:57.700 --> 05:59.620 So this should be our output. 70 06:01.100 --> 06:06.460 So I will put this create table script in insert statement in the video. 71 06:09.500 --> 06:13.940 Let me comment if there is any doubt in the solution for this. 72 06:16.500 --> 06:17.900 Thank you so much everyone. 73 06:18.540 --> 06:22.780 We will come again with the next few questions as soon as possible. 74 06:22.780 --> 06:23.500 Thank you.