SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Should be a simple query but ....

    This should be a simple query but I'm banging my head on my desk over it.

    I'm just trying to get a list of users who's email addresses match the pattern '@example.com' and who are not a member of role 21. Here's what I'm trying but it's not working:

    Code MySQL:
    SELECT u.uid, u.name, u.mail
    FROM users u
    LEFT JOIN users_roles ur
    on u.uid = ur.uid
    WHERE mail LIKE '%example.com'
      AND ur.rid != 21;

    Here's what the tables look like:
    Code MySQL:
    mysql> SELECT uid, name, mail FROM users LIMIT 5;
    +-----+------------------+-------------------------+
    | uid | name             | mail                    |
    +-----+------------------+-------------------------+
    |   1 | Admin            | [email]brian@foo.com[/email]           |
    |   3 | Kevin M.         | [email]3+nobody@bar.com[/email]        |
    |   8 | jbuckner         | [email]8+nobody@example.com[/email]    |
    |   9 | Mahmoud Ahmadian | [email]9+nobody@example.com[/email]    |
    +-----+------------------+-------------------------+
    5 rows in set (0.00 sec)
     
    mysql> describe users_roles;
    +-------+------------------+------+-----+---------+-------+
    | Field | Type             | Null | Key | Default | Extra |
    +-------+------------------+------+-----+---------+-------+
    | uid   | int(10) unsigned | NO   | PRI | 0       |       |
    | rid   | int(10) unsigned | NO   | PRI | 0       |       |
    +-------+------------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT u.uid
         , u.name
         , u.mail
      FROM users u
    LEFT OUTER
      JOIN users_roles ur
        ON ur.uid = u.uid
       AND ur.rid = 21
     WHERE u.mail LIKE '%example.com'
       AND ur.uid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect r937!

    Thanks for the help.


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
  •