ANY and ALL

[Chris2]

Hi,


Exactly. As example:

WHERE 60 <= ALL (SELECT note FROM courses WHERE studentID='Joe' )

would translate the fact that for a given student, all the courses have a
note >= 60. Someone could also express the same idea with:

WHERE 60 <= (SELECT MIN(note) FROM courses WHERE studentID='Joe' )

but that implies you mentally reversed the logic of the given rule, which
can lead to maintenance problem if the logic is complex.

Another example is about to know if all the tests had been made for a given
procedure: isTestDone is Boolean:

WHERE -1 = ALL (SELECT isTestDone FROM Tests WHERE procedureID= 4004 )

Without using the ALL quantifier... what would it be? Maybe:

WHERE NOT EXISTS( SELECT * FROM tests WHERE procedureID = 4004 AND NOT
isTestDone )

but the following won't work:

WHERE 0=(SELECT COUNT(*) FROM tests WHERE procedureID=4004 AND NOT
isTestDone )

since if there is no record, there is no row to hold the count(*). If you
want a solution based on 0 = COUNT(*), that becomes, like:

WHERE 0=(SELECT COUNT(*) FROM ( SELECT * FROM tests WHERE procedureID
= 4004 AND NOT isTestDone ) )




Hoping it may help,
Vanderghast, Access MVP

Michael Walsh,

Thanks.

I've got that copied away and will work on testing it all a little
later.

You wouldn't happen to have some DDL and sample data so I'm working
from the same point you were?


Sincerely,

Chris O.

 

[Chris2]

Yea, I found that worked too, but I couldn't quite explain why. To my mind,
'= ALL...' so by association '>=ALL' doesn't really make sense.

I haven't come across ANY and ALL before, but I'm assuming '> ALL' means the
test value must be greater than ALL the values returned by the subquery, and
ANY means the test value must be greater than at least one of the values
returned by the subquery.

Cheers,

Chris.

Chris M.,

Unfortunately, MS Access does several goofy things.

But then, it has TRANSFORM...PIVOT*, and that kind of makes up for
it (no matter how totally proprietary and low on the portability
list it is).


Sincerely,

Chris O.

* Well, it has other valuable things, too.

 

[Chris M]

Have also to add MS SQL Server does not know about Boolean, so some of the

supplied examples would work only with Jet.

Seriously???! SQL Server doesn't have a Boolean datatype? That surely can't
be true??

 

[Michel Walsh]

Hi,


========================
CurrentProject.Connection.Execute "CREATE TABLE courses (studentID TEXT(50)
NOT NULL, [note] LONG NOT NULL)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Joe', 75)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Joe', 88)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Joe', 81)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Theresa',
75)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Theresa',
85)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Henry', 55)"
CurrentProject.Connection.Execute "INSERT INTO courses VALUES('Henry', 75)"


CurrentProject.Connection.Execute "CREATE TABLE tests (procedureID LONG NOT
NULL, testID LONG NOT NULL, isTestDone LOGICAL)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4004, 102, -1)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4004, 103, -1)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4005, 101, -1)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4005, 102, -1)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4004, 103, 0)"
CurrentProject.Connection.Execute "INSERT INTO tests VALUES(4004, 104, -1)"
=====================


I was thinking of a more complex case when I said that

would not work. In this case, it *will* work.


Also, if SUM can be seen as an aggregate from +, ALL can be seen as an
aggregate from AND while ANY can be seen as an aggregate from OR.
Unfortunately, ALL and ANY are not real aggregate:
SELECT SUM(f1), ALL(f2), ANY(f3)... GROUP BY ...
would not work.


Have also to add MS SQL Server does not know about Boolean, so some of the
supplied examples would work only with Jet.




Vanderghast, Access MVP

 

[Chris M]

Seriously???! SQL Server doesn't have a Boolean datatype? That surely
can't be true??

Ahh, yes, I remember now, doesn't it have a Bit type (0 or 1) so yes, you're
right, not quite the same I guess...

 

[Chris2]

message

Hi,

========================
CurrentProject.Connection.Execute "CREATE TABLE courses (studentID TEXT(50)
NOT NULL, [note] LONG NOT NULL)"

Vanderghast, Access MVP

Michael Walsh,

Thank you.


Sincerely,

Chris O.