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 🙂

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 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/