'NOT IN' statement not working correctly in POSTGRESQL

zachs snachs 0
Viewed 31

I'm trying to find the wid (id numbers) of all members that do not have the 'Archery' or 'Swordsmanship' skill. And I need to use 'NOT IN' as part of my query.

The relevant table:

NSERT INTO WesterosiSkill VALUES
 (1001,'Archery'),
 (1001,'Politics'),
 (1002,'Archery'),
 (1002,'Politics'),
 (1004,'Politics'),
 (1004,'Archery'),
 (1005,'Politics'),
 (1005,'Archery'),
 (1005,'Swordsmanship'),
 (1006,'Archery'),
 (1006,'HorseRiding'),
 (1007,'HorseRiding'),
 (1007,'Archery'),
 (1009,'HorseRiding').
.... (continues) ....

But my query does the opposite and returns the values that actually have 'Archery' or 'Swordsmanship.' So I tried another level of negation, and a few different variations on the following query, but nothing has worked.


SELECT DISTINCT wid
FROM WesterosiSkill 
WHERE skill IS NULL
OR skill NOT IN ('Archery', 'Swordsmanship')
ORDER BY wid;

What am I missing here?

postgresql
1 Answers
SELECT DISTINCT wid FROM WesterosiSkill where wid NOT IN (SELECT wid
FROM WesterosiSkill 
WHERE skill  IN ('Archery', 'Swordsmanship'))
  1. There is no one-to-one correspondence between wid and skill, and your not in above will return a confusing value
black white0
Related