Tags

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


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

In this puzzle you have to JOIN the three tables in such a ways that the result should yield the expected output. Note you can only use a single SELECT keyword. Please see the sample input data and the expected output.

Sample Input

Table – at1

Id PID MyID
1 3 1234

Table – at2

atId ID DID
1 1 1001

Table – Prep

PrepId Nm
1001 Pawan
1234 Avtaar

Expected Output

PID Nm Nm
3 Avtaar Pawan

Script – DDL and INSERT Sample Data

--

CREATE TABLE at1
(
  Id INT
 ,PID INT
 ,MyID INT
)
GO

INSERT INTO at1 VALUES 
(1,3,1234)
GO

CREATE TABLE at2
(
  atId INT
 ,ID INT
 ,DID INT
)
GO

INSERT INTO at2 VALUES 
(1,1,1001)
GO

CREATE TABLE Prep
(
	 PrepId INT
	,Nm VARCHAR(20)
)
GO

INSERT INTO Prep VALUES
(1001, 'Pawan'),
(1234, 'Avtaar')
GO

SELECT * FROM at1
SELECT * FROM at2
SELECT * FROM Prep


--

SOLUTION 1

--

SELECT at1.PID,p1.Nm,p2.Nm
FROM 
at1 
	INNER JOIN at2 ON at1.Id = at2.Id
	INNER JOIN Prep p1 ON at1.MyID = p1.PrepId
	INNER JOIN Prep p2 ON at2.DID = p2.PrepId

--

OUTPUT – 1

--

PID         Nm                   Nm
----------- -------------------- --------------------
3           Avtaar               Pawan

(1 row 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 Performance 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