Alright, don't laugh at my query. I'm still learning MySQL and I'm pretty proud of myself for even getting this far, but it's ugly and I'm sure it's probably not the best way to accomplish this.
Here's what I'm trying to do:
I have multiple tables that I'm attempting to join to display a complete list of users and their associated information on my website. The query pulls their userid, username and e-mail address from table 1 (t1), their first and last name from table 2 (t2 & t3), their assigned role ID's from table 4 (t4) and then the appropriate role name from table 5 (t5).
The problem is that some users are assigned to multiple rows, so the resulting output of this query lists a new row for each role id/name. I would like to list multiple roles on the same row, so each users would only have one row regardless of the number of roles assigned to them.
I'm not sure if this would better be accomplished using PHP (I'm going to output the results of the query to a table) or if this can be done from within the MySQL query.
Here's my query --
SELECT t1.userid, t1.username, t1.email, t2.firstname, t3.lastname, t4.roleid, t5.groupname FROM
(SELECT ID as userid, user_login as username, user_email as email FROM wp_users) AS t1
(SELECT user_id as userid, meta_value as firstname FROM wp_usermeta WHERE meta_key = "first_name") AS t2
(SELECT user_id as userid, meta_value as lastname FROM wp_usermeta WHERE meta_key = "last_name") AS t3
(SELECT object_id as userid, group_id as roleid FROM wp_uam_accessgroup_to_object) AS t4
(SELECT ID as roleid, groupname FROM wp_uam_accessgroups) AS t5
ORDER BY t3.lastname ASC
The site is running off of Wordpress, and the roles are managed through a third-party plugin, so I can't just combine the tables or modify their structure. I need to accomplish this through a query or via php on the output/display.
I'm running MySQL 5.0.84, Apache 2.2.8, PHP 5.3.5 on CentOS 5.5.
Thanks in advance for any suggestions.