SQL Puzzle | The All values = Val1 Puzzle

In this puzzle you have to return Ids against which all the value of Val is Val1. Please check out the sample input values and sample expected output below.
For details please check out the sample input and the expected output below.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--


CREATE TABLE [1Val]
(
         Id INT
       ,Val VARCHAR(10)
)
GO

INSERT INTO [1Val] VALUES
(1,'Val1'),
(1,'Val2'),
(2,'Val1'),
(2,'Val1'),
(3,'Val1'),
(4,'Val1'),
(4,'Val2'),
(4,'Val1')
GO

INSERT INTO [1val] VALUES (5,'Val')
GO

--

—

SOLUTION – 1

--

SELECT ID FROM [1val] GROUP BY ID HAVING ( MAX(VAL) = 'VAL1' AND MIN(VAL) = 'VAL1' )

--

SOLUTION – 2

--

SELECT ID FROM [1val] GROUP BY ID HAVING COUNT(DISTINCT VAL) = 1 AND MAX(VAL) = 'VAL1'

--

SOLUTION – 3

--

SELECT ID FROM [1val] GROUP BY ID HAVING MAX(CASE WHEN VAL = 'VAL1' THEN 0 ELSE 1 END) = 0

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com