Using COUNT to compare to values

[jalbao]

i have two tables - tblSystem and tblSystemSecurity.

tblSystem consists of sSystemName and lSystemID (pk).
tblSystemSecurity consists of lUserID and lSystemID.

the tblSystemSecurity is used to keep track of which user (lUserID) has rights to access which system (lSystemID).

i need to know if a user has rights to ALL of the existing systems or SOME of the existing systems or NONE of the existing systems.

in other words, with one user in scope, i want to count how many existing systems there are and then find out how many systems the user in scope has rights to. so now we have two values (the number of systems and the number of how many the user has rights to) - comparing these two numbers i can then determine if the user has ALL, SOME or NONE access to the system.

is this possible using one query? if so, how?

tia

 

[tlbroadbent]

Try this.

Select
UserCnt, SysCnt,
Case
When UserCnt=0 Then 'None'
When UserCnt=SysCnt Then 'All'
Else 'Some'
End
From
(Select
UserCnt=Count(lSystemID),
SysCnt=(Select count(*) From tblSystem)
From tblSystemSecurity
Where lUserID=100) As qry Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.