SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast Stevenwulf's Avatar
    Join Date
    May 2002
    Location
    Berkeley
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding Users Not Associated With a Role

    I have the following tables:

    users -> users_roles <- roles

    users:
    id, name

    users_roles:
    user_id, role_id

    roles:
    id, name


    I want to find all of the users that don't have a given role.

    I can do this with a sub select, but--if possible--I'd like to use a Left Join. Is this possible?

    Can someone show me the query?

    Thanks,
    Steven

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select name 
       from users
       left join users_role on users.id = users_role.user_id
       and users_role.role_id = 4711
     where users_role.user_id is null

  3. #3
    SitePoint Enthusiast Stevenwulf's Avatar
    Join Date
    May 2002
    Location
    Berkeley
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that works!

    I had to wrap the ON clause in parenthesis to get it to work:
    ON (users.id = users_roles.user_id and users_roles.role_id = 4711)

    -Steven

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Stevenwulf View Post
    I had to wrap the ON clause in parenthesis to get it to work
    ms access, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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