Should be a simple query but

Databases
1

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:

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:

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 
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
3

Perfect r937!

Thanks for the help.