Solved

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

Posted on 2011-03-25
10
529 Views
Last Modified: 2012-06-27
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
Comment
Question by:goodie069
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35217602
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
 

Author Comment

by:goodie069
ID: 35217694
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
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35217819
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:goodie069
ID: 35217834
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
 

Author Comment

by:goodie069
ID: 35217863
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
 
LVL 5

Accepted Solution

by:
tsmgeek earned 500 total points
ID: 35217892
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
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35217897
the groupname may need you to tweek it as per the correct field name as i dont know what it is
0
 

Author Comment

by:goodie069
ID: 35217997
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
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35218027
group_concat(cast(ID AS CHAR) SEPARATOR "\n")

Open in new window

0
 

Author Comment

by:goodie069
ID: 35218081
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question