Tags

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


SQL Puzzle | The JOIN Problem – A Single SELECT | SQL Interview Question

In this puzzle you have to JOIN the three tables (Tab1,Tab2,MulTab) to get the expected output. The challenge is to do this in a single SELECT. For more details please see the sample input and expected output.

Sample Input

tab1

Id Nn
1 a
2 b
3 c

tab2

Id Nm
1 1
2 2
3 3
4 4
5 5
6 6

MulTab

tab1Id tab2Id Vals
1 1 11Val
1 2 12Val
1 4 14Val
2 1 21Val
2 4 24Val

Expected Output

Id Nn Id Nm tab1Id tab2Id Vals
1 a 1 1 1 1 11Val
1 a 2 2 1 2 12Val
1 a 3 3 NULL NULL NULL
1 a 4 4 1 4 14Val
1 a 5 5 NULL NULL NULL
1 a 6 6 NULL NULL NULL
2 b 1 1 2 1 21Val
2 b 2 2 NULL NULL NULL
2 b 3 3 NULL NULL NULL
2 b 4 4 2 4 24Val
2 b 5 5 NULL NULL NULL
2 b 6 6 NULL NULL NULL
3 c 1 1 NULL NULL NULL
3 c 2 2 NULL NULL NULL
3 c 3 3 NULL NULL NULL
3 c 4 4 NULL NULL NULL
3 c 5 5 NULL NULL NULL
3 c 6 6 NULL NULL NULL

Script – DDL and INSERT Sample Data

--

CREATE TABLE tab1 
( 
	  Id int
	, Nn varchar(10) 
)
GO

INSERT INTO tab1 VALUES 
  ( 1, 'a' )
, ( 2, 'b' )
, ( 3, 'c' ) ;

CREATE TABLE tab2 
( 
	  Id int
	, Nm varchar(10) 
)
GO

INSERT INTO tab2 VALUES 
    ( 1, '1')
  , ( 2, '2')
  , ( 3, '3')
  , ( 4, '4')
  , ( 5, '5')
  , ( 6, '6')
;

CREATE TABLE MulTab 
( 
	tab1Id int, [tab2Id] int, Vals varchar(20) 
)
GO

INSERT INTO MulTab VALUES
  (1,1,'11Val')
, (1,2,'12Val')
, (1,4,'14Val')
, (2,1,'21Val')
, (2,4,'24Val')
GO

--

SOLUTION – 1 | SINGLE Select

--

SELECT 
     a.*
    ,b.*
    ,c.*
FROM tab1 a
CROSS JOIN tab2 b 
LEFT JOIN MulTab c ON c.tab1Id = a.Id AND c.tab2Id = b.Id

--

OUTPUT – 1

--

Id          Nn         Id          Nm         tab1Id      tab2Id      Vals
----------- ---------- ----------- ---------- ----------- ----------- --------------------
1           a          1           1          1           1           11Val
1           a          2           2          1           2           12Val
1           a          3           3          NULL        NULL        NULL
1           a          4           4          1           4           14Val
1           a          5           5          NULL        NULL        NULL
1           a          6           6          NULL        NULL        NULL
2           b          1           1          2           1           21Val
2           b          2           2          NULL        NULL        NULL
2           b          3           3          NULL        NULL        NULL
2           b          4           4          2           4           24Val
2           b          5           5          NULL        NULL        NULL
2           b          6           6          NULL        NULL        NULL
3           c          1           1          NULL        NULL        NULL
3           c          2           2          NULL        NULL        NULL
3           c          3           3          NULL        NULL        NULL
3           c          4           4          NULL        NULL        NULL
3           c          5           5          NULL        NULL        NULL
3           c          6           6          NULL        NULL        NULL

(18 rows affected)

--

SOLUTION – 2 | 2 SELECTS

--


SELECT 
     a.*
    ,z.*
    ,c.*
FROM tab1 a
CROSS APPLY ( SELECT * FROM tab2 b ) z
LEFT JOIN MulTab c ON c.tab1Id = a.Id AND c.tab2Id = z.Id

--

OUTPUT – 2

--

Id          Nn         Id          Nm         tab1Id      tab2Id      Vals
----------- ---------- ----------- ---------- ----------- ----------- --------------------
1           a          1           1          1           1           11Val
1           a          2           2          1           2           12Val
1           a          3           3          NULL        NULL        NULL
1           a          4           4          1           4           14Val
1           a          5           5          NULL        NULL        NULL
1           a          6           6          NULL        NULL        NULL
2           b          1           1          2           1           21Val
2           b          2           2          NULL        NULL        NULL
2           b          3           3          NULL        NULL        NULL
2           b          4           4          2           4           24Val
2           b          5           5          NULL        NULL        NULL
2           b          6           6          NULL        NULL        NULL
3           c          1           1          NULL        NULL        NULL
3           c          2           2          NULL        NULL        NULL
3           c          3           3          NULL        NULL        NULL
3           c          4           4          NULL        NULL        NULL
3           c          5           5          NULL        NULL        NULL
3           c          6           6          NULL        NULL        NULL

(18 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