  |
Hi Doing well here at the moment, but have hit an issue with searching a group concat function using the advanced search.
Below is my query, any chance you can help?
SELECT accounts.Account_ID, accounts.Status, cus_statuses.CUS_S_Name, accounts.Acc_Type, accounts.First_Name, accounts.Last_Name, accounts.County, accounts.OfficeBelong, accounts.ShortProfile, cus_profilebuild.CUS_P_User, GROUP_CONCAT(CONCAT_WS(',', accounts.ShortProfile, cus_profilebuild.CUS_E_JInfo, cus_profilebuild.CUS_CP, cus_profilebuild.CUS_Statement, cus_profilebuild.CUS_SShort, cus_profilebuild.CUS_E_CName, cus_profilebuild.CUS_Ed_Name, cus_profilebuild.CUS_Ed_Address, cus_profilebuild.CUS_Ed_Tel, cus_profilebuild.CUS_Ed_Address, cus_profilebuild.CUS_Studied, cus_profilebuild.CUS_SkillsName, cus_profilebuild.CUS_Skills, cus_profilebuild.CUS_WEName, cus_profilebuild.CUS_WE, cus_profilebuild.CUS_AName, cus_profilebuild.CUS_Ach) separator ',') AS 'SString', GROUP_CONCAT(cus_profilebuild.CUS_Specialism separator ',') AS 'SSkills' FROM accounts LEFT JOIN cus_statuses ON accounts.Status = cus_statuses.CUS_S_ID LEFT JOIN cus_profilebuild ON accounts.Account_ID = cus_profilebuild.CUS_P_User GROUP BY accounts.Account_ID, cus_profilebuild.CUS_P_User HAVING accounts.Acc_Type = 34 AND accounts.OfficeBelong = %s
The query works fine when executed, but when I use the alias for the 'One' search it causes an issue with the query creation. I have tried it with the alias as the search column but fails and also if i reference the the whole Group Concat it creates an incorrect query for MySQL.
Values used are as follows for the filter column;
1. 'SString' (alias). Does not work when referenced as `SString`.
2. GROUP_CONCAT(CONCAT_WS(',', accounts.ShortProfile, cus_profilebuild.CUS_E_JInfo, cus_profilebuild.CUS_CP, cus_profilebuild.CUS_Statement, cus_profilebuild.CUS_SShort, cus_profilebuild.CUS_E_CName, cus_profilebuild.CUS_Ed_Name, cus_profilebuild.CUS_Ed_Address, cus_profilebuild.CUS_Ed_Tel, cus_profilebuild.CUS_Ed_Address, cus_profilebuild.CUS_Studied, cus_profilebuild.CUS_SkillsName, cus_profilebuild.CUS_Skills, cus_profilebuild.CUS_WEName, cus_profilebuild.CUS_WE, cus_profilebuild.CUS_AName, cus_profilebuild.CUS_Ach) separator ',') AS 'SString'
Regards,
Jonathan
|
|
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Login
Unread message![Read message [popular]](http://forum.dwzone-it.com/template/subSilver/images/folder_hot.gif)
Read message [popular] ![Unread message [popular]](http://forum.dwzone-it.com/template/subSilver/images/folder_new_hot.gif)
Unread message [popular]![Read message [locked]](http://forum.dwzone-it.com/template/subSilver/images/folder_lock.gif)
Read message [locked] ![Unread message [locked]](http://forum.dwzone-it.com/template/subSilver/images/folder_lock_new.gif)
Unread message [locked]