[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