Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Combine multiple MySQL query results for the same user into a single row

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
LEFT JOIN
(SELECT user_id as userid, meta_value as firstname FROM wp_usermeta WHERE meta_key = "first_name") AS t2
LEFT JOIN
(SELECT user_id as userid, meta_value as lastname FROM wp_usermeta WHERE meta_key = "last_name") AS t3
LEFT JOIN
(SELECT object_id as userid, group_id as roleid FROM wp_uam_accessgroup_to_object) AS t4
LEFT JOIN
(SELECT ID as roleid, groupname FROM wp_uam_accessgroups) AS t5
ON t4.roleid=t5.roleid
ON t3.userid=t4.userid
ON t2.userid=t3.userid 
ON t1.userid=t2.userid
ORDER BY t3.lastname ASC

Open in new window


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.
0
goodie069
Asked:
goodie069
  • 5
  • 5
1 Solution
 
tsmgeekCommented:
SELECT
    t1.userid,
    t1.username,
    t1.email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.user_id AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.user_id AND meta_key='last_name') AS last_name,
    (SELECT group_concat(tt2.roll_id) FROM wp_uam_accessgroup_to_object AS tt1 LEFT JOIN wp_uam_accessgroups tt2 ON tt1.object_id=tt2.object_id WHERE tt1.object_id=t1.userid) AS roll_ids,
    (SELECT group_concat(tt4.groupname) FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.object_id=tt4.object_id WHERE tt3.object_id=t1.userid) AS groupnames
FROM wp_users AS t1
ORDER BY lastname ASC

Open in new window


something along the lines of above, all the rolls will be concatinated into a single field with a comma seperator, as i dont have the module in question i cant check this directly but i know the concept is correct as ive done it for the first_name and last_name on my wordpress
0
 
goodie069Author Commented:
I'm getting "#1054 - Unknown column 't1.userid' in 'field list'" when I run that query.

I ran into this issue earlier with my query. For some reason I had to reverse the order of my ON statements to get it to work. Not sure how to apply that to your query, but I'll do a little trial and error.

If you have any thoughts, please let me know. Thanks for your help.
Chris
0
 
tsmgeekCommented:
SELECT
    t1.ID,
    t1.username,
    t1.email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='last_name') AS last_name,
    (SELECT group_concat(tt2.roll_id) FROM wp_uam_accessgroup_to_object AS tt1 LEFT JOIN wp_uam_accessgroups tt2 ON tt1.object_id=tt2.object_id WHERE tt1.object_id=t1.ID) AS roll_ids,
    (SELECT group_concat(tt4.groupname) FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.object_id=tt4.object_id WHERE tt3.object_id=t1.ID) AS groupnames
FROM wp_users AS t1
ORDER BY last_name ASC

Open in new window


sorry forgot that in wp_users the id field is ID but in the wp_meta its user_id
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
goodie069Author Commented:
A few field names were incorrect. I managed to get the query to execute, but the output is not correct. The "roll_ids" column displays "[BLOB - 0B]" for each row and "groupnames" displays "NULL" for each row.

SELECT
    t1.ID,
    t1.user_login,
    t1.user_email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='last_name') AS last_name,
    (SELECT group_concat(tt2.ID) FROM wp_uam_accessgroup_to_object AS tt1 LEFT JOIN wp_uam_accessgroups tt2 ON tt1.object_id=tt2.ID WHERE tt1.object_id=t1.ID) AS roll_ids,
    (SELECT group_concat(tt4.groupname) FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.object_id=tt4.ID WHERE tt3.object_id=t1.ID) AS groupnames
FROM wp_users AS t1
ORDER BY last_name ASC

Open in new window


On the bright side, only one row is being listed for each user! :-)

Let me know if you have any ideas.

Thanks,
Chris
0
 
goodie069Author Commented:
Oops, didn't see your post before I sent my reply. Looks like we were on the same wavelength, anyway.

Your updated query still produces errors, but I think that's because you still had a few bad field names. If you would, take a look at the query I posted in my last response (it executes without errors, just doesn't produce the correct result) and let me know if you have any thoughts.

Thanks again,
Chris
0
 
tsmgeekCommented:
SELECT
    t1.ID,
    t1.user_login,
    t1.user_email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='last_name') AS last_name,
    (SELECT group_concat(cast(tt2.roll_id AS CHAR)) FROM wp_uam_accessgroup_to_object AS tt1 LEFT JOIN wp_uam_accessgroups tt2 ON tt1.object_id=tt2.ID WHERE tt1.object_id=t1.ID) AS roll_ids,
    (SELECT group_concat(tt4.groupname) FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.object_id=tt4.ID WHERE tt3.object_id=t1.ID) AS groupnames
FROM wp_users AS t1
ORDER BY last_name ASC

Open in new window


note the CAST on line 7 to convert the blob to string
0
 
tsmgeekCommented:
the groupname may need you to tweek it as per the correct field name as i dont know what it is
0
 
goodie069Author Commented:
Sweet success.

SELECT
    t1.ID,
    t1.user_login,
    t1.user_email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='last_name') AS last_name,
    (SELECT group_concat(tt4.groupname) FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.group_id=tt4.ID WHERE tt3.object_id=t1.ID) AS groupnames
FROM wp_users AS t1
ORDER BY last_name ASC

Open in new window


I got rid of the Role ID's -- didn't need them once I could confirm that the correct role names were being displayed.

The output is perfect now, with the "groupnames" being separated by a comma in the event more than one group is assigned to that user. I wasn't aware of the group_concat function, so I'll definitely do some homework on that and the different formats for output. Now to figure out how to get PHP to replace those commas with line breaks!

Thanks a ton, man. I really appreciate your help.
Chris
0
 
tsmgeekCommented:
group_concat(cast(ID AS CHAR) SEPARATOR "\n")

Open in new window

0
 
goodie069Author Commented:
You are awesome! Thanks again!

Here's the final query, just for reference --

SELECT
    t1.ID,
    t1.user_login,
    t1.user_email,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='first_name') AS first_name,
    (SELECT meta_value FROM wp_usermeta WHERE user_id=t1.ID AND meta_key='last_name') AS last_name,
    (SELECT group_concat(cast(tt4.groupname AS CHAR) SEPARATOR "\n") FROM wp_uam_accessgroup_to_object AS tt3 LEFT JOIN wp_uam_accessgroups tt4 ON tt3.group_id=tt4.ID WHERE tt3.object_id=t1.ID) AS groupnames
FROM wp_users AS t1
ORDER BY last_name ASC

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now