CC V6 => Join Core Joomla User Fields to Additional User Fields

Hi
​
Additional fields are stored in table #_fields_values.
Have created a function joining #_users with #_fields_values.
The table listing returns the correct columns but only the users table has values.
Seems #_fields_values has a vertical table structure.
​
How do I join #_users to #_fields_values ?
​
Thanks

item_id is the user id

Yes, and this is how I set it up but no values in the additional fields listing
​

You need to set relation to one matching, and have a separate model for each field you want. And then in the WHERE conditions, you need
​

field1.field_id:1
field2.field_id:2

replacing the models, fields, and ids with the correct ones.
​
You need to do this because of how left joins work.
​
And then in your fields to retrieve you can do

field1.value:user.employee_id
field2.value:user.location

to retrieve the fields as user.employee_id and user.location (just as an example)

Thanks Healyhatman.
So 15 fields need 15 models ?

Yep. Only way to make it "easier" is to craft the full query yourself in PHP.

Creating a table list. Each user's name should appear only once.
Each user has multiple additional fields
​
I need help please with just the first model, say company.
For this example need to list only name and company
​
user id = id in the users table
user id = item_id in the #_fields_values table
company field_id = 6 in the #_fields_values table
​
Is this enough data to show how to get the list working for the company field?
Thanks for helping ... .
​

​

​

"Fields to retrieve" in the company model should be empty. Screenshot the users model.

​

​

Like I said you need to put in the where conditions
​

model.value:1

Replace model with company and 1 with the field id.