Adding some logic to SQL query

Question

First of all I'm really not that great at sql, with that said, onwards to the question:

Lets say I have a table called Abilities. In this example Abilities has a ID, PersonID, Text.

Lets say I do a search where I specify that I want to distinct list every person that has the ability to 'Fly', 'Drive', 'Sing' but do NOT have the ability to 'Fight', 'Skateboard'

Is there anyway to write such a query which in the end will only return the rows that match my above statement? The table and it's contents is purely fictional I might add, hence the wierdness :P

I would really appreciate help with this as it's for a rather complex search utility for a database.

Thanks

Answer 1

SELECT a.PersonId
FROM Abilities a
JOIN Abilities b ON (a.PersonId = b.PersonId AND b.text = 'Drive')
JOIN Abilities c ON (a.PersonId = c.PersonId AND c.text = 'Sing')
LEFT JOIN Abilities d ON (a.PersonId = d.PersonId AND d.text = 'Flight')
LEFT JOIN Abilities e ON (a.PersonId = e.PersonId AND e.text = 'Skateboard')
WHERE a.text = 'Fly' AND d.Id IS NULL and e.Id IS NULL

I see you got several answers trying to perform both IN and NOT IN tests on the same instance of Abilities, but that can't work - you clearly need to test on separate instances of Abilities, whence the need for this multiple self-join!

Answer 2

Depending on the actual data and query parameters, an approach without self-joins might be more efficient. I would expect this to do a single full scan of the table, whereas the join method would likely do many index lookups.

SELECT personID FROM
(
SELECT personID,
       SUM(CASE WHEN text IN ('Fly','Drive','Sing') THEN 1 ELSE 0 END) good_stuff,
       SUM(CASE WHEN text IN ('Fight','Skateboard') THEN 1 ELSE 0 END) bad_stuff
  FROM abilities
  GROUP BY personID
)
WHERE good_stuff = 3 and bad_stuff = 0