SQL Query to get only those rows with only 1 value in another column

Question

I have a table like this:

I need a query to return only those ProjectID's that have only State=21. i.e I want only ProjectID 2 & 5.

I do not want those records with ProjectID 1, 3, 4 & 6, because in those case, the state is also equal to other numbers

Answer 1

Reasoning goes like

• In a subselect, select all ProjectID's that have a state = 21
• In the outer select, retain only those ProjectID's that only have a state = 21 using a HAVING clause

SQL Statement

SELECT  ProjectID
FROM    table t1
        INNER JOIN (
          SELECT  ProjectID
          FROM    table
          WHERE   state = 21
        ) t2 ON t2.ProjectID = t1.ProjectID
GROUP BY
        ProjectID
HAVING  COUNT(*) = 1        

Answer 2

Select all the projects with a single state value, which ('the value') is, by the way, equal to the specified one:

SELECT   ProjectID
FROM     atable
GROUP BY ProjectID
HAVING   COUNT(*) = 1
   AND   MAX(State) = @State

You can also use MIN, SUM, or AVG to check the value of State with the same effect (because it should be the only value).