Return a hardcoded value if the result set follows a set of conditions (T-SQL-2000)

This question refers to the T-SQL compatible with MSSMS 2000.

Suppose the result set of a query Q always returns one column with 0, 1, or n records开发者_高级运维. I want a superquery W to return a value of 1 if the following conditions hold:

1. Only one record was retrieved in the subquery Q
2. The one record that was retrieved is 'c'

Q = SELECT DISTINCT status_code FROM Student

W(Q) = ?

If Q is a general query and not necessarily the one in your question, the putting status_code instead of column will do:

CASE WHEN  (SELECT COUNT(*) FROM Q) = 1
       AND (SELECT COUNT(*) FROM Q WHERE column='c') = 1
     THEN 1
     ELSE 0
END

Try this:

select if(count(*)=1 and status_code='c',1,0) as W from Student

(If you have 2 rows with status_code='c', it will return 0. Is it the expected output ?)