 Hello, welcome to SSUnitech. So, see this side and this is continuation of SQL Server interview questions and answers. So, today we gonna discuss about a very tricky question where we want to apply filters by status. So, let's move to another slide where we will understand about the question. So, here as we can see we have input table that contains two columns. First is customer ID, second is product ID and we want to extract those records where customer contains product ID A and B and not contains C. So, as we can see for ID 1 we are having product ID A, B, D and E. It have A and B but it doesn't have C. So, we want in our output. Let's move to ID 2. So, for customer ID 2 we can see we are having A, B, C. So, we are having our first condition A and B but our second condition is not going to satisfied because it has C. So, we don't want in our output. Let's move to ID 3. So, in case of ID 3 we are having A, B, X, Y. So, it has A and B but it doesn't have C. So, we want customer ID 3 in our output. So, how we can achieve this output? Let's move to Sikosov Management Studio where we will write the query to get this output. So, as I am using SQL 2014 you may have another version of Sikosov but query would be same for all the versions. So, here we need to execute our first query to create the table. So, I want to execute our created statement for creating the table of customer product table. So, command completed successfully. So, table has been created successfully. Now, we need to insert the record on it. So, once we execute our inserted statement, then we can see 11 rows affected. So, data have been inserted successfully in our table. Now, we can select data from the table and we can check. So, select star from our table, then we can execute our selected statement. Now, we can see we are having same metadata as we have displayed in our slide. So, what approach we will follow? So, let's comp in this record. So, go to on Excel to understand about the logic. So, here I want to use self going. So, we can paste one time, then second time, and then our third time. So, this is for A and this is for B. So, once we apply management between our A table and B table and in our A table we will check record for the A and in our B table we will check record for the B. If A is our label in our table A and B is our label in our table B, then we want those records. So, in our output we will get customer ID, then the product ID. So, here we can see we are having one with A in our A table and in our B table we are having one with B. So, in our output we will get one. Once we move to ID 2, then we can see we are having A and we are having B. So, we will get in our output. Once we move to our C, then we are having A in our A table and B in our B table. So, 3 will also move in our output. In our third table, which is our C table and it will pick record for the C and then we can apply left join. Once we apply left join, if the customer ID is null in our C table, then we want those customer IDs. So, customer ID 1 does not have product ID C. So, we want in our output. So, customer ID 1 will be reflected from here. Once we move to customer ID 2, then we can see it has C. So, we do not want in our output. Once we move to third, then it also does not have C. So, we want in our output. So, in our output we will get 1 and 3 and we can apply sub query from our table A where customer ID is 1 and 3. So, we want those records. So, this is the approach we will follow. So, go to our SQL Server Management Studio. Here we can write the query to get the output. So, as we have discussed, this is our table A and then we can apply inner join. So, this is our inner join with the same table and this time this is for B. And here we want to condition if the A.Customer ID is equals to B.Customer ID and A.Product ID should be equals to A and B.Product ID should be equals to B. So, we want those records. Once we execute, then we can see we are getting 1, 2, 3. Now, we need to apply left join. So, this is our left join with the same table and this time this is for C. And we can apply A.Customer ID should be equals to C.Customer ID and we want C.Product ID should be equals to C. Now, we can execute our selected statement. So, here we are getting customer ID null 2 and null for table C. So, once we apply filter where C.Customer ID is null, then we will get our output as we were expected. Now, here we can select our customer ID. We can execute our selected statement. So, we are getting ID 1 and 3. Now, we can select the data from our customer product table and where customer ID in this selected statement. Now, we can execute and we can check. So, we are getting ID 1 and 3. So, we are getting this output by using subquery. Now, we want this output without using our subquery. So, we can copy our selected statement which we have written and I want to put this selected statement in a derived table and put an alias with B. And here I want to use select as tick from our table which is our customer product tbl and we can put an alias with A. And now, we can use inner join with our table B on A.Customer ID should be equals to B.Customer ID. And here we want to get records from table A only. Now, we can execute our selected statement. So, we are getting same metadata as we were getting by using subquery. And here we are getting this output by using joins. So, this is from join. According to interval question, we can write these queries to get the output. 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.