Adding some logic to SQL query

Question

Hi,

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

You didn't specify the database server, but I know this works in SQL Server

SELECT a1.ID, a1.PersonID, a1.Text
FROM Abilities a1
WHERE a1.Text IN ('Fly', 'Drive', 'Sing')
AND NOT EXISTS (SELECT ID
                FROM Abilities a2
                WHERE a2.ID = a1.ID
                AND a2.Text IN ('Fight', 'Skateboard'))

Answer 2

There are a number of ways to solve this. I've selected one which should be readable, but not necessarily maintainable for a lot of change.

WITH Fliers AS (
    SELECT PersonID
    FROM Abilities
    WHERE Text = 'Fly'
),
Drivers AS (
    SELECT PersonID
    FROM Abilities
    WHERE Text = 'Drive'
),
Singers AS (
    SELECT PersonID
    FROM Abilities
    WHERE Text = 'Sing'
),
Fighters AS (
    SELECT PersonID
    FROM Abilities
    WHERE Text = 'Fight'
),
Skateboarders AS (
    SELECT PersonID
    FROM Abilities
    WHERE Text = 'Skateboard'
)
SELECT *
FROM People
INNER JOIN Fliers
    ON Fliers.PersonID = People.PersonID
INNER JOIN Drivers
    ON Drivers.PersonID = People.PersonID
INNER JOIN Singers
    ON Singers.PersonID = People.PersonID
LEFT JOIN Fighters
    ON Fighters.PersonID = People.PersonID
LEFT JOIN Skateboarders
    ON Skateboarders.PersonID = People.PersonID
WHERE Fighters.PersonID IS NULL
    AND Skateboarders.PersonID IS NULL

But there's lots of ways to skin this cat.

Most of the solutions here use a simple IN, NOT IN, but they are not going to give the results I think you are expecting - i.e. people who have Fly AND Drive AND Sing AND NEITHER Fight NOR Skateboard

Answer 3

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 4

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