You should be able to do it 1 query, I can think of two ways. I'm not sure which one will run faster. I use table aliasing to keep
the lines shorter.
The first possibility would have user permissions in one column and group
permissions in another:
SELECT users.userid,perm_u.permissions,perm_g.permissions
FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid
LEFT JOIN permissions AS perm_u ON u_p.permid=perm_u.permid
LEFT JOIN users_usergroups AS u_ug ON users.userid=u_ug.userid
LEFT JOIN usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid
LEFT JOIN permissions AS perm_g ON ug_p.permid=perm_g.permid
WHERE users.userid=#
But if you want to just have one column of permissions, you'll need to use
UNIONs. I think this is more readable.
SELECT users.userid AS userid,permissions AS permissions
FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid
LEFT JOIN permissions ON u_p.permid=permissions.permid
WHERE users.userid=#
UNION
SELECT users.userid AS userid,permissions
FROM users
LEFT JOIN users_usergroups AS u_ug ON users.userid=u_ug.userid
LEFT JOIN usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid
LEFT JOIN permissions AS perm_g ON ug_p.permid=perm_g.permid
WHERE users.userid=#
Either of those should work. I don't know your table structure, so I can't get
too specific with it.
----- Original Message -----
From: "Stephen Orr" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, August 30, 2006 7:27 PM
Subject: Complex SQL for multiple joins
Hi,
For my current website project I'm developing a fine-grained access control
system.
I have the schema mostly worked out, although it will be having additional
data added to parts of it later.
For the record, I have 6 tables:
users (contains all my individual users)
usergroups (contains all the groups users can belong to)
permissions (contains all the things that the system allows)
users_usergroups (a join table that identifies which users belong to which
groups)
users_permissions (another join table that identifies which users have which
permissions)
usergroups_permissions (the final join table that identifies which
usergroups have which permissions)
Each of the _permissions join tables has an additional type column
specifying whether the link allows or denies that particular action.
What I've managed to get so far is queries returning which users belong to
which groups, which users have which permissions, and which usergroups have
which permissions. However, I need to go one step further and retrieve the
permissions belonging to the usergroups which a specified user is a member
of. Ideally I'd like to retrieve the individual users permissions at the
same time.
So what I need is a query that returns permissions belonging to a specific
user, and permissions belonging to the usergroups that the specified user is
a member of. Is this something I can do without too much hassle?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
