Results 1 to 4 of 4

Thread: Sql Query Help!

  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: Sql Query Help!

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    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#> );


    Last edited by LKBrwn_DBA; 10-09-06 at 18:22.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2005
    Posts
    25
    Thanks alot LKBrwn_DBA...works PERFECTLY!


  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Glad it worked for you! -- Good luck.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •