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.

 1 Yes 2 Yes 1 No 1 No 3 No 4 Yes

Expected Output

For Yes

 2 Yes 4 Yes

For No

 1 Yes 1 No 3 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”.

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