Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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