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 |
CodePudding user response:
I believe you just had the wrong alias used:
select distinct surveydataid
from surveydata sd
where not exists (
select *
from surveydata sd2
where sd2.surveydataid=sd.surveydataid
and sd2.choseninterests in (2,3)
)
CodePudding user response:
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)
);