Tags

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


SQL Puzzle | The Conditional JOIN Puzzle | SQL Interview Question

In this puzzle you have to JOIN the two tables based on the condition. The condition is like if the Id column in the OtherDetails IS NOT NULL then you have to JOIN on the ID column, If the Id column in the OtherDetails IS NULL then you have to JOIN on the Val column. Columns required in the output are – Id, Val and FullName. For more details please see the sample input and expected output.

Sample Input

Table 1 : SCodes

Id Val
1 MH
2 MH
6 HR
3 JK
5 JK
4 JK

Table 2 : OtherDetails

Id Val FullName
NULL MH Maharashtra
2 MH Mumbai
NULL HR Haryana
NULL JK Jammu & Kashmir
3 JK J & K
4 JK Vadi

Expected Output

Id Val FullName
1 MH Maharashtra
2 MH Mumbai
3 JK J & K
4 JK Vadi
5 JK Jammu & Kashmir
6 HR Haryana

Script – DDL and INSERT Sample Data

--

CREATE TABLE SCodes 
(
	 Id INT
	,Val VARCHAR(2)
)
GO

CREATE TABLE OtherDetails
(
	 Id INT
	,Val VARCHAR(2)
	,FullName VARCHAR(50)
)
GO

INSERT INTO SCodes VALUES
(1,'MH'),
(2,'MH'),
(6,'HR'),
(3,'JK'),
(5,'JK'),
(4,'JK')
GO

INSERT INTO OtherDetails VALUES
(NULL,'MH','Maharashtra'),
(2,'MH','Mumbai'),
(NULL,'HR','Haryana'),
(NULL,'JK','Jammu & Kashmir'),
(3,'JK','J & K'),
(4,'JK','Vadi')
GO

SELECT * from SCodes a
GO

SELECT * from OtherDetails a
GO

--

SOLUTION – 1

--

;WITH CTE AS 
(
	SELECT a.*,b.FullName
	FROM SCodes a
	INNER JOIN OtherDetails b ON a.Id = b.Id
)
,CTE1 AS
(
	SELECT a.*,b.FullName
	FROM SCodes a
	INNER JOIN OtherDetails b ON b.Val = a.Val AND b.Id IS NULL
	WHERE NOT EXISTS ( SELECT NULL FROM OtherDetails x WHERE x.ID = a.ID )
)
SELECT * FROM CTE
UNION 
SELECT * FROM CTE1

--

Output – 1

--

Id          Val  FullName
----------- ---- --------------------------------------------------
1           MH   Maharashtra
2           MH   Mumbai
3           JK   J & K
4           JK   Vadi
5           JK   Jammu & Kashmir
6           HR   Haryana

(6 rows affected)

--

SOLUTION – 2

--

;WITH CTE AS 
(
	SELECT a.*,b.FullName , ROW_NUMBER() OVER (PARTITION BY a.Id,a.Val ORDER BY b.Id DESC) rnk
	FROM SCodes a
	INNER JOIN OtherDetails b 
	ON ((a.Id = b.Id AND b.Id IS NOT NULL) OR (b.Val = a.Val AND b.Id IS NULL))

)
SELECT Id,Val,FullName FROM CTE
WHERE rnk = 1

--

Output – 2

--

Id          Val  FullName
----------- ---- --------------------------------------------------
1           MH   Maharashtra
2           MH   Mumbai
3           JK   J & K
4           JK   Vadi
5           JK   Jammu & Kashmir
6           HR   Haryana

(6 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements