Hi, Enjoy learning the just purchased ARI Smart Content. Love that all the export buttons work.

I recreated using the same code used in the old AriTables. Want to list user accounts with the columns listed below but having problems retrieving the column that have several values #joomprofile_field_values. but the Contact Phone & Last Name column is blank. The field_values has up to 10 values for each user. Your advice will be appreciated.

Under the old AriTables I was able to see the Contact Phone and Last Name with the code used below.

My current code:

SELECT
#_users.name as "Name",
max(case when #_joomprofile_field_values.field_id = 3 then value end) AS "Last Name",
#_usergroups.title as "Member Type",
#_users.email as "Email",
max(case when #_joomprofile_field_values.field_id = 16 then value end) AS "Contact Phone"

FROM #_users

JOIN #_user_usergroup_map
ON #_users.id=#_user_usergroup_map.user_id

JOIN #_usergroups
ON #_user_usergroup_map.group_id=#_usergroups.id

JOIN #_joomprofile_field_values
ON #_users.id=#_joomprofile_field_values.user_id

WHERE ((#_usergroups.id = 2) or (#_usergroups.id = 12) or (#_usergroups.id = 13) or (#_usergroups.id = 14)) AND (#_users.block=0)

GROUP BY #_joomprofile_field_values.user_id

===============================

I think I solved it. the code used now:

old code: max(case when #_joomprofile_field_values.field_id = 3 then value end) AS "Last Name",

working code: MAX(CASE WHEN #_joomprofile_field_values.field_id = 3 THEN #_joomprofile_field_values.value ELSE NULL END) AS last_name,

Hello,

You can use IN operator to specify several values. For example



Regards,
ARI Soft

I'll give it go next week. Thx