Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Question Unanswered: Return Users Not In A Group

    I need some assistance creating a query. I have users table, groups table, and UsersGroups table. Basically a single user could belong to one or more groups. I need to be able to return the users that are not in a specified groups. Table def are below.

    USERS:
    --------
    ID (Primary Key)
    Username
    Password


    GROUPS:
    ------------
    ID (Primary Key)
    GroupName



    USERSGROUPS:
    -----------------
    ID (Primary Key)
    GroupID
    UserID


    Any help is greatly appreciated!
    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pebkac
    I need to be able to return the users that are not in a specified groups.
    how are you going to specify which group -- by id or by name?

    note that the primary key of the usersgroups table is, shall we say, less than optimal -- remove the ID and make the composite (GroupID,UserID) the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    35
    Groups are specified by ID. Meaning:

    GROUPS:
    ------------
    ID (Primary Key)--------------------
    GroupName |
    |
    |
    |
    USERSGROUPS: |
    ----------------- |
    ID (Primary Key) |
    GroupID < ------------------------
    UserID


    Unfortinatly it is not my database. It is one already in existance so I can't change the attributes of the table to make the composite (GroupID,UserID) the primary key.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select u.Username
         , u.Password
      from USERS as u
    left outer
      join USERSGROUPS as ug
        on ug.UserID = u.UserID
       and ug.GroupID = 937
     where ug.UserID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    35

    Thumbs up Genious!

    GENIOUS!!!! -- Thanks for the help, seems to work great!

Posting Permissions

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