Geek Answers Handbook

SQL statement and ALL

Looking for an elegant way around this ...

DECLARE @ZIP INT SET @ZIP = 55555 IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!'

The problem is that if (SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) does not return any records, then the above IF evaluates to true. I am looking for a way to make this evaluate to false when there are no records returned by the ALL subquery.

My current solution:

 DECLARE @ZIP INT SET @ZIP = 55555 DECLARE @ALLZIPS TABLE (INT ZIP) INSERT INTO @ALLZIPS SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1 IF EXISTS(SELECT TOP 1 * FROM @ALLZIPS) AND (@ZIP = ALL (SELECT ZIP FROM @ALLZIPS)) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!'
+4
source share
3 answers

Jumping in:

 IF (SELECT SUM(CASE WHEN ZIP = @ZIP THEN 0 ELSE 1 END) FROM PEOPLE WHERE PERSONTYPE = 1) = 0 PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!'
+2
source

Using:

 IF EXISTS(SELECT NULL FROM PEOPLE p WHERE p.persontype = 1 HAVING MIN(p.zip) = @Zip AND MAX(p.zip) = @Zip) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!'
+3
source

Consider also using EXISTS.

 IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) AND EXISTS(SELECT 1 FROM PEOPLE WHERE PERSONTYPE = 1)
+2
source

More articles:


All Articles