This question is an exact duplicate of:
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?
</div>
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