Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 23/11/2024 Black Friday 2024

    BIG SALE, 30% discount for all our extensions. Use BF24 coupon code. Hurry up the discount is valid till 3 December.

  • 31/12/2023 New Year SALE

    We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

SQL code to join table with mulitiply value column
(1 viewing) (1) Guest
Go to bottomPage: 1
TOPIC: SQL code to join table with mulitiply value column
#67407
SQL code to join table with mulitiply value column 3 Years, 9 Months ago Karma: 0
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

===============================
The administrator has disabled public write access.
 
#67408
Re: SQL code to join table with mulitiply value column 3 Years, 9 Months ago Karma: 0
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,
The administrator has disabled public write access.
 
#67409
Re: SQL code to join table with mulitiply value column 3 Years, 9 Months ago Karma: 760
Hello,

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

Code:


SELECT
 id,
 title
FROM
 #__table
WHERE
 id IN (5, 10, 15)



Regards,
ARI Soft
The administrator has disabled public write access.
 
#67415
Re: SQL code to join table with mulitiply value column 3 Years, 9 Months ago Karma: 0
I'll give it go next week. Thx
The administrator has disabled public write access.
 
Go to topPage: 1