SitePoint Sponsor

User Tag List

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

    Need help selecting a list of users and their associated roles

    Here's what I'm looking for: "List of users and their roles where role id = 4 or 5 or 6;

    Code MySQL:
    mysql> SELECT List of users in role 4, 5 or 6;
    +-----+------------------+
    | rid | r.name  | u.name |
    +-----+------------------+
    |5    | editor  | Bob    |
    |5    | editor  | Randy  |
    |5    | editor  | Jeff   |
    |5    | editor  | Susan  |
    |4    | manager | Sara   |
    }6    | writer  | Bob    |
    |6    | writer  | Susan  |
    +-----+---------+--------+
    7 rows in set (0.00 sec)

    Here are the tables I'm working with:

    Code MySQL:
    mysql> describe users;
    +-------+------------------+------+-----+---------+-------+
    | Field | Type             | Null | Key | Default | Extra |
    +-------+------------------+------+-----+---------+-------+
    | uid   | int(10) unsigned | NO   | PRI | 0       |       | 
    | name  | varchar(64)      | NO   | UNI |         |       | 
    +-------+------------------+------+-----+---------+-------+
    2 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)
     
    mysql> describe role;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | rid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
    | name  | varchar(64)      | NO   | UNI |         |                | 
    +-------+------------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)

    Thanks!

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Figured it out:
    Code MySQL:
    mysql> SELECT ur.rid, r.name AS 'role', u.uid, u.name
        -> FROM users_roles ur
        -> LEFT JOIN users u
        -> ON u.uid = ur.uid
        -> LEFT JOIN role r
        -> ON r.rid = ur.rid
        -> WHERE ur.rid = 281
        ->   OR ur.rid = 5
        ->   OR ur.rid = 10
        ->   OR ur.rid = 61
        ->   OR ur.rid = 261
        ->   OR ur.rid = 41
        ->   OR ur.rid = 71
        ->   OR ur.rid = 3
        ->   OR ur.rid = 51
        ->   OR ur.rid = 11
        ->   OR ur.rid = 31
        -> ORDER BY ur.rid, u.uid ASC;


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
  •