SQL Puzzle | The Name Puzzle

Here you have given 3 tables, You have to join three table on the basis of ID. Now the catch here is if the name present in table 3 (Three) then we have to take name from three, If not then we will have to move to Table 2 (Two) and if not then move to Table 1 (One)

Please check out the sample input and the expected output below-

Sample Input

Table 1

ID Name
1 Pawan
2 Sharlee
3 Rahisha
4 Disha
5 Ramesh
6 Kishan
7 Qoutilya
8 Sandeep

Table 2

ID Name
1 Diljit
2 Kumar
3 Khowal

Table 3

ID Name
1 MSBISKILLS.COM
6 MSBI

Expected Output

ID Name
1 MSBISKILLS.COM
2 Kumar
3 Khowal
4 Disha
5 Ramesh
6 MSBI
7 Qoutilya
8 Sandeep

Solution 1

 ``` -- SELECT a.ID,COALESCE(u.NAME,t.NAME,a.NAME) Name FROM One a OUTER APPLY ( SELECT TOP 1 b.Name FROM Two b WHERE a.ID = b.ID )t OUTER APPLY ( SELECT TOP 1 c.Name FROM Three c WHERE a.ID = c.ID )u -- ```

Solution 2

 ``` -- SELECT a.ID , CASE WHEN c.NAME IS NOT NULL THEN c.NAME WHEN b.NAME IS NOT NULL THEN b.NAME ELSE a.Name END Name FROM One a LEFT JOIN Two b ON a.ID = b.ID LEFT JOIN Three c ON a.ID = c.ID -- ```

Solution 3

 ``` -- SELECT a.ID , COALESCE(c.NAME,b.NAME,a.NAME) Name FROM One a LEFT JOIN Two b ON a.ID = b.ID LEFT JOIN Three c ON a.ID = c.ID -- ```

Below 3 Solutions are provided by a Senior MVP.

Solution 4

 ``` -- /* Using Windows Functions */ SELECT distinct [id], first_value([name]) over (partition by [id] order by [level] desc) as [name] FROM ( SELECT 3 as [level], [id], [name] FROM [THREE] UNION ALL SELECT 2 as [level], [id], [name] FROM [TWO] UNION ALL SELECT 1 as [level], [id], [name] FROM [ONE] ) AS A -- ```

Solution 5

 ``` -- /* Using FULL OUTER JOIN */ WITH CTE_STEP1 AS ( SELECT COALESCE(A.ID, B.ID) AS ID, COALESCE(A.NAME, B.NAME) AS NAME FROM [THREE] AS A FULL OUTER JOIN [TWO] AS B ON A.ID = B.ID ), CTE_STEP2 AS ( SELECT COALESCE(C.ID, D.ID) AS ID, COALESCE(C.NAME, D.NAME) AS NAME FROM CTE_STEP1 AS C FULL OUTER JOIN [ONE] AS D ON C.ID = D.ID ) SELECT * FROM CTE_STEP2 ORDER BY ID GO -- ```

Solution 6

 ``` -- SELECT * INTO #TEMP1 FROM [THREE] AS A; GO -- Select new ids from 2 INSERT INTO #TEMP1 SELECT * FROM [TWO] AS B WHERE B.ID NOT IN (SELECT ID FROM #TEMP1); GO -- Select new ids from 1 INSERT INTO #TEMP1 SELECT * FROM [ONE] AS C WHERE C.ID NOT IN (SELECT ID FROM #TEMP1); GO -- Show results SELECT * FROM #TEMP1 AS D ORDER BY D.ID; GO -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com