 Hello, welcome to SSUnitedSocialListSide and this is the contribution of SQL Server interview questions and answers. So today we are going to discuss one more interview question that is total by multiple tables. So how we can get the totals if we have more than one table. So let me move to next slide to understand about the question. So as we can see over here, we are having three input tables. First is table A, then table B and table C. So all these tables are having two columns, first is id, second is the salary. In the output column, we are having only two columns that is id and salary. So we just want to combine all the salaries according to their id. So as we can see in table A, we are having id1, then table B, id1, table C, id1. So salaries for id1 is 100, 100 and 100 for each table. So here we can see in the output for id1 we are having 300. So we are going to combine all the salaries according to their id. But we can see here we are having id5 which is not available in table A and table B. That is only available in the table C. So we also want those id which is available in any of these three tables. So how we can get the output by using SQL queries. So here write any two methods to get the output. So let me move to SQL server management studio to write the query for getting this output. So here we need to write two methods for getting this output. So as I am using SQL 2017, you may have another version of SQL server but this query will work for all the versions. So first of all I would like to create these three tables. So let me execute the create statement for creating table A, table B and table C. So as we can see command completed successfully. So tables have been created successfully. Now let me execute the insert statement to insert the data on the tables. So we can execute these three insert statements. Table A is having 4 rows, table B is having 3 rows and table C is also having 4 rows. So we can write the select statement to check the data into the tables. So this is for table A. Now let me complete the select statement and paste two times for B and C. So this would be for B and then this would be for C. So now let me execute this select statement to check the data. So as we can see here for table A we are having ID, cell D then table B and table C. So we are having the same metadata as we seen in the slide. So for method 1 we just want to combine by using union all with all the values from these three tables. So how we can get that for method 1? So we can write for method 1 here we can use the CT. So let me use the CT then as inside that CT we just want to combine the data from these three tables. So let me complete these three tables and paste it here and let me put the union all between these three. So this is union all then again union all. If we will execute these three tables with the union all then we are getting all the IDs with the cell D. So next step we just want to combine all the cell D. So let me execute this select from CT and here we just want ID then sum of cell D. So this is the sum of cell D. So we can use the cell D and here we have to use the group by. So this is the group by and this group by should be on ID column. Now let me execute this query and here we can see we are having 1, 2, 3, 4 and 5, 300, 600, 900, 400 and 500. So let me compare with the slides data. So 300, 6, 900, 400 and 500. So we are getting the output as we were expected. So this is the method 1. So how we can write the second method to getting this output? So here we can write the second method. So second method is method 2. So here we just want to use the joints. So how we can use the joints? So we have to use the full joint. So let me use the first table that is part 39A and then we have to use the full joint with the B and C. So let me use the full joint with the part 39B this time and let me put an alias with the B. Now on A.ID then that ID should be equals to B.ID. Next we just want to use the full joint with the third table. So that is table C. Now we can put an alias with the C on A.ID that ID should be equals to C.ID. Now in the selected statement as of now if we just want to get all the columns from all the tables. So let me execute this. So here as we can see we are getting the ID, ID and ID. So we want to get the ID from the table A.If that id is null then look into the table B. If that is also null then we want to get the ID from table C. So for that we can use the colas function. So let me use the colas. So this is colas. So first of all I just want to get the ID from table A if that is null then look into the table B. If that is also null then from the table C. So here Golis is going to check the id from the table A. If that value is null then look into the table B. If that is also null then only look into table C. So it will be going to first not null value from the left side. Then we can put as id. Next we just want to combine the salaries. So as we can see salary from table A, salary from table B and salary from table C. So here again we have to use the is null function because we are having the null values and if we will combine the null with the value then output would be null. So is null A dot salary comma 0. If salary is null from the table A then that would be the 0. Then we need to check for table B as well. So B dot salary if that is null then put as 0. Then I need to check for third table that is table C. Then we have to use the is null then C dot salary and if that is null then we want to as 0. Then this will be the salary. Now let me execute this query and we just want to check the output. So as we can see we are getting the output as we were expected. So 300, 600, 900, 400 and 500. If we compare with the slides data then 36, 9, 400 and 500. So both are same. So these two methods by which we can get the output as we were expected. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much. In next video.