Select only if count==2 SQL Server 2008

i would like to select only on the condition t开发者_如何学运维hat another select == 2

for example

select field1 from table1 where count((select field2 from table2)) = 2

is this possible?

You should look into the HAVING CLAUSE of SQL's GROUP BY.

e.g.

SELECT * FROM TABLE1
WHERE <MYKEY> IN (
   SELECT <MYKEY>
   FROM TABLE2
   GROUP BY <MYKEY> HAVING COUNT(*) = 2 --Better to use > 1
)

select field1 from table1 where (select count(field2) from table2)=2

try it out:

SET NOCOUNT ON
declare @a table (RowID int)
insert @a values(1);insert @a values(2);insert @a values(3);
declare @b table (RowID int)
insert @b values(10);insert @b values(20)
SET NOCOUNT OFF

select a.RowID FROM @a a WHERE 2=(SELECT COUNT(RowID) FROM @b)

delete @b where RowID=20

select a.RowID FROM @a a WHERE 2=(SELECT COUNT(RowID) FROM @b)

OUTPUT:

RowID
-----------
1
2
3

(3 row(s) affected)

(1 row(s) affected)

RowID
-----------

(0 row(s) affected)