SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem doing query with lookup table

    Hello all,
    I'm having a difficult time wrapping my head around a particular query I'm trying to do between a regular table and a lookup table. On this site I'm working on I'm creating a feature that will allow adding users to groups. I'm using a lookup table for this since a user can be in more than one group. On the page for adding the users to the groups, I want to list only the users who are not yet in that group so they do not get added twice. My query works fine if there is only one user in the group, but if there is more than one the result shows all users plus the ones that are not in the group multiple times. Here is my query:
    Code:
    SELECT user_id, fname, lname, groupid, userid FROM wpuser, grpusr_lookup WHERE meade_wpuser.user_id != meade_grpusr_lookup.userid AND meade_grpusr_lookup.groupid = $gid ORDER BY meade_wpuser.lname ASC
    wpuser is the table that lists all the users and grpusr_lookup is the lookup table that contains only groupid to identify the group and userid for the id from the user table.
    I have 3 users right now in my list (for testing purposes) so if I query using a group id for a group that has two users in it, those two users show up in the list and the user who is not in the list shows up twice. Again, it works find if there is only one user in the group. Can anyone see how I could do this query differently?
    Thanks!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    To find all users that aren't present in the group-users table, you can't use an (implicit) INNER JOIN, but you'll have to use a LEFT JOIN. See http://dev.mysql.com/doc/refman/5.1/en/join.html for more info.

    Code:
    SELECT 
        user_id
      , fname
      , lname
      , groupid
      , userid 
    FROM wpuser
    LEFT JOIN grpusr_lookup 
    ON meade_wpuser.user_id != meade_grpusr_lookup.userid 
    AND meade_grpusr_lookup.groupid = $gid
    WHERE meade_grpusr_lookup.userid IS NULL
    ORDER BY meade_wpuser.lname ASC

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    To find all users that aren't present in the group-users table, you can't use an (implicit) INNER JOIN, but you'll have to use a LEFT JOIN. See http://dev.mysql.com/doc/refman/5.1/en/join.html for more info.

    Code:
    SELECT 
        user_id
      , fname
      , lname
      , groupid
      , userid 
    FROM wpuser
    LEFT JOIN grpusr_lookup 
    ON meade_wpuser.user_id != meade_grpusr_lookup.userid 
    AND meade_grpusr_lookup.groupid = $gid
    WHERE meade_grpusr_lookup.userid IS NULL
    ORDER BY meade_wpuser.lname ASC
    Hmm, that didn't quite do it. For the groups that had just one user it showed that user (the opposite of what I wanted to happen) and for groups with more than one user it didn't show anything at all. There would never be an instance of the lookup table having a NULL value because an entry only gets added with the group id and the user id together. I tried changing it to IS NOT NULL but that gave me the same results as my original query. Thanks for the attempt, though. Any other ideas?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Sorry, you have to change != into = in the ON clause.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Sorry, you have to change != into = in the ON clause.
    Aha! That did it. Thanks so much for that. My brain gets all muddled sometimes when queries get complex. I guess I had better study this stuff more!
    Thanks again.


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
  •