Thursday 16 April 2015

Interview Question - How to combine result and display where the data is coming from

This one is the interview question faced by my friend during project allocation interview. 

We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.


 


The query needs to be written to produce the output given below







My first reaction was to use UNION and then think about displaying pseudo Result Column. I tried using it for 2-3 minutes and it wasn't going anywhere so changed my approach came up with the answer.. 

First let's prepare the table with the required data.

DECLARE @TAB1 TABLE(ID1 SMALLINT
)
DECLARE @TAB2 TABLE(ID2 SMALLINT
)
INSERT INTO @TAB1SELECT UNION ALLSELECT UNION ALLSELECT 3

INSERT INTO @TAB2SELECT UNION ALLSELECT UNION ALLSELECT 3

--Given below are the 3 approaches that I could think of right now

-- First Attempt using FULL OUTER JOIN
SELECT CASE
  
WHEN T1.ID1 IS NULL THEN T2.ID2
  ELSE T1.ID1
END AS ID,CASE
  
WHEN T1.ID1 IS NULL THEN 'IN T2'
  
WHEN T2.ID2 IS NULL THEN 'IN T1'
  
ELSE 'IN BOTH'END AS Result
FROM @TAB1 T1
FULL OUTER JOIN @TAB2 T2
ON T1.ID1 T2.ID2

-- Second attempt using INTERSECT,EXCEPT and UNION ALL
SELECT FROM (
(
  
SELECT *,'IN T1' AS RESULT FROM @TAB1
  
EXCEPT
  SELECT 
*,'IN T1' FROM @TAB2)

  
UNION ALL

  (
  
SELECT *,'IN BOTH' FROM @TAB1
  
INTERSECT
  SELECT 
*,'IN BOTH' FROM @TAB2)

  
UNION ALL

  (
  
SELECT *,'IN T2' FROM @TAB2
  
EXCEPT
  SELECT 
*,'IN T2' FROM @TAB1)
TEST

-- Third attempt using IN, NOT IN AND UNION ALL
SELECT *,'IN T1' AS RESULT FROM @TAB1WHERE ID1 NOT IN SELECT ID2 FROM @TAB2 )
UNION ALL
SELECT ID1,'IN BOTH' FROM @TAB1WHERE ID1 IN (SELECT ID2 FROM @TAB2)
UNION ALL
SELECT *,'IN T2' FROM @TAB2
WHERE ID2 NOT IN SELECT ID1 FROM @TAB1 )
  

No comments:

Post a Comment

bloggerwidgets