1 00:00.360 --> 00:02.480 Let's solve another problem. 2 00:03.120 --> 00:08.760 Find the number of employees reporting to each manager. 3 00:09.320 --> 00:12.880 Below is the problem statement given a single table of employees. 4 00:17.440 --> 00:19.680 That contains employee ID employee name. 5 00:21.800 --> 00:22.840 And manager ID. 6 00:23.240 --> 00:27.200 Write a query to find the number of employees who report to each manager. 7 00:27.840 --> 00:28.480 So. 8 00:30.600 --> 00:35.840 Let me copy this data and let me ask to the ChatGPT. 9 00:38.960 --> 00:48.600 So I am having the table structure and data as per below. 10 00:51.080 --> 00:51.840 Let's see. 11 00:52.320 --> 00:53.960 Okay it is saying. 12 00:56.320 --> 00:58.880 Please go ahead and share your table structure and data. 13 00:59.520 --> 01:00.360 Okay fine. 14 01:00.800 --> 01:09.490 Now I am I have shared my table structure and data and it will give me the data into the tabular format. 15 01:14.730 --> 01:16.890 Now let me ask the problem statement. 16 01:17.770 --> 01:19.330 So what is my problem statement? 17 01:19.330 --> 01:21.210 This is my problem statement right. 18 01:22.930 --> 01:27.570 We have to find the number of employees who report to each manager. 19 01:30.170 --> 01:32.850 So let me ask the problem statement. 20 01:37.010 --> 01:39.530 And let's see whether it has given the correct. 21 01:42.170 --> 01:43.290 Result or not. 22 01:44.370 --> 01:48.930 Yeah it is given that solution number one. 23 01:52.050 --> 01:55.410 If you also want to include managers with zero reports okay. 24 01:55.970 --> 01:58.370 It is given the second solution as well. 25 02:01.170 --> 02:08.290 So let's copy this solution and let me check what it is saying. 26 02:09.490 --> 02:11.810 So let me execute this one. 27 02:15.980 --> 02:16.420 Okay. 28 02:16.460 --> 02:20.220 So first manager is having three employees reporting to them. 29 02:20.580 --> 02:25.500 And Henry is having one number of employee reporting to them. 30 02:26.020 --> 02:32.860 And if you see over here this solution and above solution is almost the same right. 31 02:36.700 --> 02:40.260 So let me execute both of the query at the same time. 32 02:42.900 --> 02:46.020 See both of the query is giving the same result. 33 02:46.460 --> 02:49.700 Now what is the second solution is saying. 34 02:50.700 --> 02:54.580 So let me copy this code and let me. 35 02:58.860 --> 02:59.140 Okay. 36 02:59.180 --> 03:02.180 Over here it has used the left join right. 37 03:02.620 --> 03:12.260 So see over here what it is retrieving over here it is retrieving those employee who are having no manager 38 03:12.260 --> 03:13.020 reporting. 39 03:13.180 --> 03:15.420 Sorry no employees reporting to them. 40 03:15.740 --> 03:18.380 So it means zero employee is reporting to them. 41 03:18.860 --> 03:22.750 Those managers also it is showing like this way, right? 42 03:23.110 --> 03:24.990 But actually, this is not required. 43 03:24.990 --> 03:27.030 This is not a correct answer. 44 03:27.030 --> 03:28.790 We can say, right. 45 03:35.510 --> 03:37.030 This is not a correct answer. 46 03:37.230 --> 03:46.190 So if you go to our this query and if you comment this portion, then then I think also it is giving 47 03:46.190 --> 03:48.510 the okay. 48 03:48.550 --> 03:50.190 It is not giving the same result. 49 03:50.230 --> 03:54.070 Now let's see what is the difference between this one and. 50 03:56.550 --> 03:57.070 This one. 51 03:57.110 --> 03:57.590 Okay. 52 03:58.030 --> 04:00.950 Left join is used over here in both the cases. 53 04:08.670 --> 04:10.790 Let me execute the query again. 54 04:13.670 --> 04:14.070 Okay. 55 04:14.110 --> 04:18.790 The difference over here is it has interchanged the table over here. 56 04:19.230 --> 04:20.510 So it has interchanged. 57 04:20.510 --> 04:22.910 The table means employee EMP table. 58 04:22.910 --> 04:26.070 It is considering over here as a left table. 59 04:26.430 --> 04:28.600 And employee table is considering. 60 04:28.600 --> 04:30.560 Over here is the right table. 61 04:31.000 --> 04:33.320 And over here it is a reverse case. 62 04:33.800 --> 04:40.720 So if I do over here like M and let me do it over here as E. 63 04:41.520 --> 04:43.440 And now let me try to check. 64 04:46.120 --> 04:47.160 Both the queries. 65 04:48.600 --> 04:48.960 So see. 66 04:48.960 --> 04:50.520 Now it is giving the same result. 67 04:50.520 --> 04:50.960 Right. 68 04:51.320 --> 04:56.600 So so we have to identify actually what is our actual requirement. 69 04:59.920 --> 05:08.280 But I think our actual requirement is satisfying with this one only we do not need uh, those manager 70 05:08.920 --> 05:11.240 to whom zero employees are reporting. 71 05:11.280 --> 05:13.480 We do not need that particular data. 72 05:14.160 --> 05:14.640 Right. 73 05:14.960 --> 05:18.040 So this query is satisfying our answer. 74 05:20.280 --> 05:20.600 Right. 75 05:20.640 --> 05:21.520 And how it works. 76 05:21.520 --> 05:27.240 So I have already explained you in the last couple of videos how this query will work. 77 05:27.240 --> 05:27.680 Right. 78 05:29.760 --> 05:34.240 So we will see the next question into the next lecture. 79 05:34.240 --> 05:34.840 Thank you.