0

I have a mysql "users" table with:

id | name
1  | Jane Doe

and a second table "custom_user_fields" (related with userid):

id | userid | fieldname | value
1  | 1      | country   | us
2  | 1      | address   | test address

With PHP I have to create a list with all users and its related fields (if possibile with one single query).

Expected result:

id | name     | country | address
1  | Jane Doe | us      | test address

It's possible?

ipel
  • 1,326
  • 1
  • 18
  • 43

1 Answers1

1

This is possible for a pre-defined set of related fields in table custom_user_fields. You can use conditional aggregation:

SELECT u.id, u.name,
       MAX(CASE WHEN fieldname = 'country' THEN value END) AS country, 
       MAX(CASE WHEN fieldname = 'address' THEN value END) AS address
FROM users AS u
LEFT JOIN custom_user_fields AS c ON u.id = c.userid 
GROUP BY u.id, u.name
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98