 Hello, welcome to SSUnitech. Sushil this site and this is continuation of SIKO Swabo interview questions and answers. So today we are going to discuss one more interview question that is multi-level parent-child relationship. So what is this question? So let's move to next slide to understand about the question. So here as we can see we are having input table that would have only two columns. First is parent, second is child. In the output it would have four columns. First is child, then level 1, then level 2 and level 3. So level 1 is the father of child column, level 2 is the father of level 1 column, level 3 is the father of level 2 column. So we are going to get data up to level 3. So here we will also have a parameter that is used to filter for the child. Here we just want to get the data for child H, G and B. So we can apply filter by using that parameter. So how we can write the query for getting this output? Let's move to SIKO Swabo Management Studio. So as I am using SQL 2012 you may have another version of SIKO Swabo. So first of all let me execute this create table statement to create the table. So command completed successfully. So table is created. Now I just want to insert data on this table. So we can execute this insert statement. So 11 rows selected. So data have been inserted successfully. Now I just want to check the data by using this selected statement. So here we are having the same meta data as we seen in the slide. So first of all let me copy this and go to Excel to understand about the logic. So here I just want to create the copy of this table by three times. So we will use the self join between the table. So this is our table one and this is table two and last is table three. So first of all in the output as we seen we are having child then level one then level two then level three. So we just want to get data from the table one directly for child and parent. So we can look for H. So child is H and in level one we can directly get D. Now we need to concentrate on level two. How we can get the data from table two column. So we just want to apply join between parent of level one with the child of level two. So here we have D in the table one for the parent of H then in the table two we are having D which is the child. So we need to apply join between these two and that join should be left join because if data is not available in the table two but we just want to those records as well. So here we can get the parent of table two that is B that should be the level two. That is fine. Now we need to concentrate on level three in level three as we can see we have table three. So we need to apply join between table two and table three. What would be the joining column. So parent of table two and child of table three. So here we have B we can see we are also having B. So this would be the joining column and we need to get the record from table three that is parent that is A. So this is the logic by which we can get the output. So we can check for H in the slide and we are having H D B and A H D B and A. So by using this logic we can directly calculate the output as we are expecting. So go to our SQL server to write the query. So first of all here as we have table one that is A. Now I just want to get data for A.child and A.parent and this parent should be level one as we seen in the excel. Now we need to use the left join and this left join with the existing table. Now let me call this table as B. What would be the joining column. So joining column should be A.parent should be equals to B.child. So this is for level two and in the select column we can directly get data from table B.parent and it should be level two. Now we can use level three as well. So we can write left then join. Let me call this as C that is table three. Now B.parent should be equals to C.child. Now that is fine and here we can get the C.parent and that would have level three. Now all good. So we can directly use where clause where A.child that should be H then G and B. We can check in the slide. So H, G and B. Now let me execute this query and check the output. So here as we can see we are having B, A then null and null. So in the slide we are having B, A then null and null that is fine G, E, B, A. So G, E, B, A. Now H, D, B, A. So this is the query by which we can directly get the parent child relationship up to level three. So I hope you have understand how we can write the query for getting this output. So 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. If you need this script it is available on the description of this video. So you can copy from there. Thank you so much for watching.