khgove khgove - 4 months ago 21
SQL Question

SQL select statement with except

I'm trying to generate a SQL statement where I need to get rid of all 'users' that have a certain trait attributed to them. Here is the example here

+------+-------+
| User | Trait |
+------+-------+
| A | Fire |
| A | Water |
| A | Air |
| B | Water |
| B | Air |
| C | Water |
| C | Fire |
+------+-------+


With SQL I'd like to remove all users who have the trait fire associated with them.

So basically, afterwards, we'd be left with

+------+-------+
| User | Trait |
+------+-------+
| B | Water |
| B | Air |
+------+-------+


If I was able to use something in excel to filter it out instead of through SQL, this would work as well. I've been looking through various ways, but from what I've tried, most will only remove the single row with the trait, but not the user along with it.

I need sql to translate something in the lines of

For (i = table.length; i++)
If Trait = Fire
getVal(User(i))
DeleteRows(User(i))


I'm looked into sql except, but the table I'm using is quite a bit more complex, so some help using a basic example would be nice to lead me in the right direction.

Thanks

Answer

You can use a sub-select and discard userids with NOT IN

SELECT *
FROM mytable
WHERE userid NOT IN (SELECT userid FROM mytable WHERE Trait = 'Fire')
Comments