I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).

The database structure is as follows:

id | user_id | meta_key | meta_value

sample data is as follows:

1 | 3434 | first_name | Brandon
2 | 3434 | last_name  | Johnson
3 | 3434 | street_add | 123 main
4 | 3434 | city       | ocean beach
5 | 3434 | state      | Texas

I am trying to return the first name, last name, street_add, city, and state in one sql statement.

I'd like the output to look like:

Brandon, Johnson, 123 Main, Ocean Beach, Texas 
  • Do all user_ids respect the same order,first comes firstname than lastname and so on,ordered by id?You could use SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t GROUP BY user_id – Mihai Sep 30 '14 at 0:01
  • When you say "one query" do you mean "one row?" An example of your expected output would be helpful. – Michael Green Sep 30 '14 at 0:35
  • Michael Green - I am just looking to pull all the records in a single sql statement. I updated the post to show the desired output. I'd like to pull all the user's info from the database in the output like above to export to an excel sheet – brandon-estrella-dev Sep 30 '14 at 1:08
  • Mihai - There are more records in the meta_value column than just first name, last name, address, city, state and zip. However, those are the only records I am looking to pull. – brandon-estrella-dev Sep 30 '14 at 1:16
  • SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t WHERE meta_key IN('first_name','last_name',etc) GROUP BY user_id – Mihai Sep 30 '14 at 14:24
up vote 8 down vote accepted

Just use GROUP_CONCAT with a WHERE condition

SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) 
FROM t 
WHERE meta_key  IN('first_name','last_name','street_add','city','state') 
GROUP BY user_id

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.