CodeIgniter Forums

Full Version: MYSQL Query help
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]mjsilva[/eluser]
Hi there,

I'm a newb when I have to do SQL queries, I have this tables:

tab_Roles:
Code:
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| role_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(255) | NO   |     | NULL    |                |
| module       | varchar(255) | NO   |     | NULL    |                |
| action       | varchar(255) | NO   |     | NULL    |                |
| description  | varchar(255) | NO   |     | NULL    |                |
| creationDate | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+


tab_Users:
Code:
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| user_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username      | varchar(255) | NO   |     | NULL    |                |
| password      | varchar(255) | NO   |     | NULL    |                |
| realname      | varchar(255) | NO   |     | NULL    |                |
| creationDate  | datetime     | NO   |     | NULL    |                |
| lastLogonDate | datetime     | NO   |     | NULL    |                |
| lastIpAddress | varchar(255) | NO   |     | NULL    |                |
| expireDate    | datetime     | NO   |     | NULL    |                |
| email         | varchar(255) | NO   |     | NULL    |                |
| disabled      | tinyint(1)   | NO   |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+


tab_user_roles:
Code:
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| role_id | int(11) | NO   | PRI | 0       |       |
| user_id | int(11) | NO   | PRI | 0       |       |
+---------+---------+------+-----+---------+-------+

As you should have understood by now I user this tables to associate users to roles and roles to users.

I've wrotte a query to retrive the associted roles from a specifiq user, the query is:

Quote:SELECT tab_roles.role_id, tab_roles.module, tab_roles.action, tab_roles.description
FROM tab_users, tab_roles, tab_user_roles
WHERE (tab_users.user_id = tab_user_roles.user_id AND tab_roles.role_id = tab_user_roles.role_id) AND tab_user_roles.user_id = $user_id


Now I need a query that return all Roles that are not associated with user X

Can someone help me with this?

Thanks

El Forum

[eluser]mjsilva[/eluser]
Got it:

Code:
SELECT * FROM tab_roles WHERE role_id NOT IN (SELECT role_id FROM tab_user_roles WHERE user_id = $user_id)