Select values in SQL that do not have other corresponding values except those that i search for

Question

I have a table in my database:

Name  |  Element
 1         2
 1         3
 4         2
 4         3
 4         5

I need to make a query that for a number of arguments will select the value of Name that has on the right side these and only these values. E.g.: arguments are 2 and 3, the query should return only 1 and not 4 (because 4 also has 5). For arguments 2,3,5 it should return 4.

My query looks like this:

SELECT name FROM aggregations WHERE (element=2 and name in (select name from aggregations where element=3))

What do i have to add to this query to make it not return 4?

Answer 1

A simple way to do it:

SELECT name
FROM aggregations
WHERE element IN (2,3)
GROUP BY name
HAVING COUNT(element) = 2

If you want to add more, you'll need to change both the IN (2,3) part and the HAVING part:

SELECT name
FROM aggregations
WHERE element IN (2,3,5)
GROUP BY name
HAVING COUNT(element) = 3

A more robust way would be to check for everything that isn't not in your set:

SELECT name
FROM aggregations
WHERE NOT EXISTS (
  SELECT DISTINCT a.element 
  FROM aggregations a
  WHERE a.element NOT IN (2,3,5)
  AND a.name = aggregations.name
)
GROUP BY name
HAVING COUNT(element) = 3

It's not very efficient, though.

Answer 2

This isn't tested, but usually I would do this with a query in my where clause for a small amount of data. Note that this is not efficient for large record counts.

SELECT ag1.Name FROM aggregations ag1 
WHERE ag1.Element IN (2,3)
AND 0 = (select COUNT(ag2.Name) 
 FROM aggregatsions ag2 
 WHERE ag1.Name = ag2.Name
  AND ag2.Element NOT IN (2,3)
)
GROUP BY ag1.name;

This says "Give me all of the names that have the elements I want, but have no records with elements I don't want"

Answer 3

Create a temporary table, fill it with your values and query like this:

SELECT  name
FROM    (
        SELECT  DISTINCT name
        FROM    aggregations
        ) n
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    (
                SELECT  element
                FROM    aggregations aii
                WHERE   aii.name = n.name
                ) ai
        FULL OUTER JOIN
                temptable tt
        ON      tt.element = ai.element
        WHERE   ai.element IS NULL OR tt.element IS NULL
        )

This is more efficient than using COUNT(*), since it will stop checking a name as soon as it finds the first row that doesn't have a match (either in aggregations or in temptable)