SQL Puzzle | The YesNo Puzzle

Puzzle Statement

In this puzzle you have to accept an input parameter. The value of the input parameter can be Yes or No. The input table contains 2 columns Id, Bits.

If the user passes No value as the input parameter then we should get all Ids where at least one Bits value is No. E.g. For Id = 1 we have bits Yes & No. So all distinct Ids = 1 should come as the output.

If the user passes Yes value as the input parameter then we should get all Ids where we don’t have a single Bits value as No. E.g. For Id = 2 we have don’t have a single bits value as No. So Id = 2 should come as the output.

Please checkout Sample input and expected output for Yes and No.

Expected Output

For Yes

For No

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE YesNo
(
	 Id SmallInt
	,Bits VarChar(3)
)
GO

INSERT INTO YesNo
VALUES
(1, 'Yes'),
(2, 'Yes'),
(1, 'No'),
(1, 'No'),
(3, 'No'),
(4, 'Yes')
GO

SELECT * FROM YesNo
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Except & Intersect

--

DECLARE @Input AS VARCHAR(3) = 'Yes'

IF @Input = 'Yes'
	BEGIN
		SELECT DISTINCT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo
			EXCEPT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
	END
ELSE IF @Input = 'No'
	BEGIN
		SELECT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo WHERE Bits = 'Yes'
			INTERSECT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
		UNION
		SELECT ID , Bits FROM YesNo WHERE Bits = 'No'
	END

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com