SQL PUZZLE | The Intersection of the Ids Puzzle – Multiple Methods | SQL Interview Question

Well It is another really good interesting interview questions I have. You have to find out Id where ValId IN 2 OR 3 and then ValId IN 1 OR 4 and then you need to find out the common Id between them. For more details please see the sample input and expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

CREATE TABLE TheIntersect
(
	 Id INT
	,ValId INT	
	,Descr VARCHAR(10)
)
GO

INSERT INTO TheIntersect VALUES
(123,1,'A'),
(144,1,'B'),
(105,2,'C'),
(101,2,'D'),
(102,3,'E'),
(102,3,'F'),
(101,4,'G')
GO

SELECT * FROM TheIntersect
GO

--

SOLUTION – 1

--

SELECT Id
FROM TheIntersect
GROUP BY Id
HAVING 
   (
	   (COUNT(CASE ValId WHEN 1 THEN 1 END) > 0 OR  COUNT(CASE ValId WHEN 4 THEN 1 END) > 0)
   AND (COUNT(CASE ValId WHEN 2 THEN 1 END) > 0 OR  COUNT(CASE ValId WHEN 3 THEN 1 END) > 0)
   )

--

Output – 1

--

Id
-----------
101

(1 row affected)



--

SOLUTION – 2

--

SELECT Id FROM TheIntersect I
WHERE ValId IN ( 1 , 4 )
AND EXISTS 
(
	SELECT NULL FROM TheIntersect b
	WHERE ValId IN ( 3 , 2 )
	AND I.Id = b.Id
) 

--

Output – 2

--

Id
-----------
101

(1 row affected)

--

SOLUTION – 3

--

SELECT x.Id FROM
(
	SELECT Id FROM TheIntersect
	WHERE ValId IN ( 1 , 4 )
)x
INNER JOIN 
(
	SELECT Id FROM TheIntersect
	WHERE ValId IN ( 3 , 2 )
)u ON u.Id = x.Id

--

Output – 3

--

Id
-----------
101

(1 row affected)



--

SOLUTION – 4

--

SELECT Id FROM TheIntersect
WHERE ValId IN ( 1 , 4 )
INTERSECT
SELECT Id FROM TheIntersect
WHERE ValId IN ( 3 , 2 )

--

Output – 4

--

Id
-----------
101

(1 row affected)


--

Perfomance Note – As per my laptop and data Query3 is performing best of the LOT.

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