O P O P - 6 months ago 9
SQL Question

Select results if table doesn't contain criteria

table

id | term
---+-----
1 | 2015
2 | 2015
3 | 2016


I have this table and want to select all 2015 results:

select * from table where term = 2015


HOWEVER, I only want it to to return 2015 results IF 2015 is the only term that shows up in the table. If it has anything else, it should not return any rows.

For example, the current table should return nothing since
id=3
has a term of
2016
. If
id=3
had a term of
2015
, I'd want it to show all
2015
results.

How can I accomplish this.

Answer

You can use NOT EXISTS for this:

SELECT * 
FROM table 
WHERE term = 2015 
  AND NOT EXISTS (SELECT 1 
                  FROM table 
                  WHERE term <> 2015
                  )

Demo: SQL Fiddle

NOT EXISTS evaluates to true if any records are returned by the subquery. This is a simple example, and the functionality can be expanded by correlating the subquery to the outer query.