0

I am trying to get all jomsocial users into a json webservice.
I have tried and get all users details by using select query below.

SELECT * FROM #_community_users

But i have some extra user fields in to the #_community_fields_values table.

My question is how to get user details into the #_community_users table as well as same userid extra filed values into the #_community_fields_values table via a single query.

I have tried below code,

SELECT * FROM #_community_fields_values, #_community_users, #_users 
WHERE #_users.id = #_community_users.userid 
AND #_community_users.userid = #_community_fields_values.user_id

But it retrieves repeated values.

So how to get users details like avatar, name and users extra fields like address, gender, about me into a same query without repeat values.

I thought i have explained well, if any one want more details means i am ready to give.

Please any one help me to write a query to get all values without repeating usersinfo.

Nizam
  • 5,698
  • 9
  • 45
  • 57
Delphin Sam
  • 142
  • 2
  • 12

1 Answers1

0

You will always get 'repeated values' when you join one->many. You won't get completely duplicated rows unless you have, for example, duplicate #_community_fields_values data related to the same #_community_users row.

It's the logical output and you cannot avoid this. You could look at either aggregating your data with something like GROUP_CONCAT() and a GROUP BY #_community_users.userid or you can process it into a usable form in your application (PHP) code.

phil-lavin
  • 1,187
  • 7
  • 19
  • Thanks for your quick response. Could you please send some sample code using GROUP BY means it will be very useful for me. – Delphin Sam Dec 19 '13 at 09:07
  • I have tried a query using GROUP BY like below, select *, (select value FROM hoa_community_users as u, hoa_community_fields as a LEFT JOIN hoa_community_fields_values as b ON a.id = b.field_id WHERE a.id=3 AND b.field_id = 3 AND b.user_id =u.userid) AS birthdate FROM hoa_community_fields_values as c WHERE c.user_id = 608 GROUP BY c.user_id. but it showing error like "#1242 - Subquery returns more than 1 row". – Delphin Sam Dec 19 '13 at 09:19