How to select rows where multiple values do not exist

Scenario: Charlie, Amy and Robert have each filled out a survey which asks the user to select all the sports they are interested in. The question is multiple choice. In the table surveyData each row represents one of the answers each user chose. so Charlie(0001) chose basketball, rugby, acrobatics and darts.

I would like to select from the table all surveyDataIds (users) who did not choose exactly basketball (0002) and rugby (0003). I believe what I’m trying to do here is perform a NAND type operation.

Desired result: When querying this table I would expect to return the following surveyDataIds: 0002 and 0004. surveyDataIds would need to be grouped as to not have duplicates. Robert is not returned as he selected basketball (0002).

Here is what I have tried so far, taking advice from the answer in this post SELECT WHERE multiple records don’t exist. Unfortunately, it does not work and is returning the wrong results.

select *
FROM surveyData sd
WHERE NOT EXISTS (
    SELECT 1
    FROM surveyData sd2
    WHERE sd.surveyDataId = sd2.surveyDataId AND sd.chosenInterests in (2, 3)
)

0001 = Charlie
0002 = Amy
0003 = Robert
0004 = Lauren

interest options
0 = tennis
1 = football  
2 = basketball
3 = rugby  
4 = snooker  
5 = acrobatics 
6 = bowling 
7 = squash  
8 = cricket 
9 = darts   
10 = javelin

Table name: surveyData

surveyDataId	chosenInterests
0001	2
0001	3
0001	5
0001	9
0002	6
0002	7
0002	9
0002	1
0002	4
0002	8
0003	2
0003	7
0004	10

Answer

Use NOT IN:

select distinct surveyDataId
FROM surveyData
WHERE surveyDataId NOT IN (
    SELECT surveyDataId
    FROM surveyData
    WHERE chosenInterests in (2,3)
);

BTW your query with EXISTS would work too:

select distinct surveyDataId
FROM surveyData sd1
WHERE NOT EXISTS (
    SELECT *
    FROM surveyData sd2
    WHERE sd1.surveyDataId = sd2.surveyDataId and chosenInterests in (2,3)
);

Advertisement

Answer