Solved

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

Posted on 2011-03-25
10
499 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
  • 5
  • 5
10 Comments
 
LVL 5

Expert Comment

by:tsmgeek
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:goodie069
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Accepted Solution

by:
tsmgeek earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
group_concat(cast(ID AS CHAR) SEPARATOR "\n")

Open in new window

0
 

Author Comment

by:goodie069
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now