SQL Puzzle | The NO Root Puzzle – A SINGLE Select | SQL Interview Question

In this puzzle you have to find out nodes that doesn’t have root in database tree. The challenge is to do this in a single select. For more details please see the sample input and expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

CREATE TABLE TheLeftRight 
(
   PId INT NOT NULL
  ,[Left]  INT 
  ,[Right] INT 
)
GO

INSERT INTO TheLeftRight VALUES
(1,2,3),
(2,4,5),
(3,6,7),
(10,NULL,NULL),
(9,8,NULL)
GO

SELECT * FROM TheLeftRight
GO


--

SOLUTION – 1

--

SELECT T1.PId
FROM  TheLeftRight U
RIGHT JOIN TheLeftRight T1
ON (u.[Left]=T1.PId or u.[Right]=T1.PId )
WHERE u.PId IS NULL

--

OUTPUT – 1

--


PId
-----------
1
10
9

(3 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.

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