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

Id ValId Descr
123 1 A
144 1 B
105 2 C
101 2 D
102 3 E
102 3 F
101 4 G

Expected Output

Id
101

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 🙂

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/