Tags

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


T-SQL Query | [ The Double Join Puzzle ]  – In this puzzle we have to join below 3 input tables and get the desired output. Please check out the sample input and expected output for details.

Sample Input

EmployeeDlts

ID Name
1 Pawan
2 Neeraj
3 Isha

EmployeeProject

1 Microsoft
1 Google
1 HortonWorks
2 Microsoft

EmployeeSkills

ID Skill
1 SQL
1 MSBI
2 SQL
2 SSRS

Expected Output

Id Name Project Skill
1 Pawan Microsoft SQL
1 Pawan Google MSBI
1 Pawan HortonWorks NULL
2 Neeraj Microsoft SQL
2 Neeraj NULL SSRS

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE EmployeeDlts(
ID INT,
Name VARCHAR(10)
)
GO

INSERT INTO EmployeeDlts(ID,Name)
SELECT 1,'Pawan'  UNION ALL
SELECT 2,'Neeraj' UNION ALL
SELECT 3,'Isha'
GO

CREATE TABLE EmployeeSkills(
ID INT,
Skill VARCHAR(10)
)
GO

INSERT INTO EmployeeSkills(ID,Skill)
SELECT 1,'SQL'  UNION ALL
SELECT 1,'MSBI'  UNION ALL
SELECT 2,'SQL' UNION ALL
SELECT 2,'SSRS'

CREATE TABLE EmployeeProject(
ID INT,
Project VARCHAR(15)
)
GO

INSERT INTO EmployeeProject(ID,Project)
SELECT 1,'Microsoft'  UNION ALL
SELECT 1,'Google'  UNION ALL
SELECT 1,'HortonWorks' UNION ALL
SELECT 2,'Microsoft'

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/



;WITH CTE1 AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY %%Physloc%%) rnk FROM EmployeeSkills s 
)
,CTE2 AS 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY %%Physloc%%) rnk FROM EmployeeProject p 
)
SELECT ISNULL(a.ID,b.Id) Id, d.Name , b.Project, a.Skill  FROM CTE1 a FULL OUTER JOIN CTE2 b ON a.ID = b.ID AND a.rnk = b.rnk 
LEFT JOIN EmployeeDlts d ON d.ID = ISNULL(a.ID,b.ID)


--

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

Keep Learning

http://MSBISkills.com