Adding some logic to a SQL query

Question

First of all, I'm really not so good at sql, with this being said, and to the question:

Say I have a table called "Abilities." In this example, the Vulnerability has an ID, PersonID, Text.

Let’s say that I’m doing a search in which I indicate that I want to distinguish a list of each person who has the ability to "fly", "drive", "sings", but does not have the ability to "fight", "skateboard",

Is it possible to write a query that will eventually return strings matching my statement above? The table and its contents are purely fictional, I could add, therefore, wierdness: P

I would really like to help with this, as it is for a rather complicated search utility for the 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 that you have several answers trying to run the IN and NOT IN tests of the same instance of capabilities, but this will not work - you obviously need to test separate instances of the capabilities, which is why the need for this plural I-join!

Answer 2

Depending on the actual data and query parameters, an approach without independent connections may be more effective. I expect this to do one full table scan, while the join method is likely to do a lot of 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

Answer 3

. , , .

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

.

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

Answer 4

SELECT * FROM ( SELECT PERSONID, COUNT(SKILLS)  FROM DUMMY  WHERE SKILLS IN ('FLY','DRIVE','SING') GROUP BY PERSONID HAVING COUNT(*)=3 ) A 

WHERE PERSONID NOT IN 

 ( SELECT DISTINCT PERSONID  FROM DUMMY WHERE SKILLS  IN ('FIGHT','SKATEBOARD')  )

Answer 5

You did not specify a database server, but I know that it 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'))