query custom fields joomla

Question

I have added a custom fields on the user management, which works greats, the custom fields are save on fields fields_values tables, I don't find a way to join the info from user table users table and the fields tables in one query results.

I need something like: SELECT id,NAME,username,customfield FROM users WHERE Id=2

How can I achieve that?

Answer 1

SELECT u.*, field_id as fid, value as fvalue FROM `#__users` as u  LEFT JOIN `#__fields_values` AS f ON u.id = f.item_id WHERE u.id=2

// u as user table
// f as fileds table
// u.id as userid (user table user id)
// f.item_id as userid (fields table user id)