7

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 
asked Sep 29, 2014 at 23:21
6
  • 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 Commented Sep 30, 2014 at 0:01
  • When you say "one query" do you mean "one row?" An example of your expected output would be helpful. Commented Sep 30, 2014 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 Commented Sep 30, 2014 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. Commented Sep 30, 2014 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 Commented Sep 30, 2014 at 14:24

1 Answer 1

9

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
answered Sep 30, 2014 at 17:36

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.