Hey all,

Am struggling to write a query that returns the correct result!

I have 2 tables:

A user table (parent) and a permissions table (child).

- The user table consists of a list of all users of my system
- The permissions table consists of a list of activities that a user/user type may perform
-userid in the premissions table may be left blank as we could set permissions for a specific activity by user type

My question is : How do we retrieve which users do not have permissions for a particular activity???

I have tried many ways - but no luck so far. Some of my methods:

1) Select userid from users left join permissions on users.userid=permissions.userid where activity<>1 or userid is null

2) Then using subqueries to select all userid which did not exist in the subquery - no luck either!!!

Any help/hints/advice will be GREATLY appreciated!

sub

One way could be:

Code:

Select Userid From Users 
Where NOT EXISTS (
Select NULL From Permissions 
 Where Permissions.Userid = Users.Userid
   And Activity = <insert here the activity#> );

Thanks alot LKBrwn_DBA...works PERFECTLY!

Glad it worked for you! -- Good luck.