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

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

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        

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).

select ProjectID,count(State) from table group by ProjectID having count(state)=1;