[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to use GROUP_CONCAT in a basic join select, with AS too.

Posted on 2011-03-23
6
Medium Priority
?
836 Views
Last Modified: 2012-06-27
hi again,
many thanks to angellll for the tip - only one problem now - i do not know what the hell to do with it in my situation! a cross table select, as follows gives errors and i do not understand why and what needs to change. on top of that i need to have the IF() field selected AS concatenated too - but i am entirely stumped with this new information! :(
SELECT 
	36_serviceattributegroups.text_AttributeGroupDescription, 
	36_serviceattributegroups.bigint_OwnerKeyID, 
	3_serviceattributes.text_AttributeDescription, 
	GROUP_CONCAT(
		36_serviceattributegroups.bigint_AttributeIncludedID 
		SEPARATOR ", "), 
	#GROUP_CONCAT(
	#	IF(
	#		36_serviceattributegroups.bigint_AttributeIncludedID, 
	#		(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID), 
	#		NULL
	#	) AS text_AttributeParentDescription 
	#	SEPARATOR ", "), 
	3_serviceattributes.bigint_AttributeServiceID 
FROM 
	3_serviceattributes, 
	36_serviceattributegroups 
GROUP BY 
	36_serviceattributegroups.bigint_OwnerKeyID 
WHERE 
	3_serviceattributes.bigint_AttributeServiceID = 1 AND 
	3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID;

Open in new window

this returns
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 3_serviceattributes.bigint_AttributeServiceID = 1 AND 3_serviceattribu' at line 20
what am i doing wrong? and the commented part needs to be included as well - that returns the following error in PMA:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS text_AttributeParentDescription SEPARATOR ", "), 3_serviceattributes.big' at line 13
help please!!!! i cannot seem to get this function to work in this context :/
and googling for a result in this context - does not seem to turn anything up either.
the reference does not say much regarding this context either. -_-
0
Comment
Question by:intellisource
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 

Author Comment

by:intellisource
ID: 35202105
umm please refer to this post which is my current problem. i do not understand how to use the GROUP_CONCAT function in the context i need it in! can one use it twice in a query?
0
 

Author Comment

by:intellisource
ID: 35202135
lol damnit - i meant this post.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35202231
SEPARATOR ", "  => must be SEPARATOR ', '

the WHERE part shall be BEFORE the GROUP BY

then, the IF() function needs a boolean / expression as first argument ...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:intellisource
ID: 35202728
oki thankyouuuu! XD
been an awesome lot of help since my tired mind does not want to go further :P
0
 

Author Comment

by:intellisource
ID: 35202765
ummm sighs...
the query:
SELECT 
	36_serviceattributegroups.text_AttributeGroupDescription, 
	36_serviceattributegroups.bigint_OwnerKeyID, 
	3_serviceattributes.text_AttributeDescription, 
	GROUP_CONCAT(
		36_serviceattributegroups.bigint_AttributeIncludedID 
		SEPARATOR ', '), 
	GROUP_CONCAT(
		IF(
			(36_serviceattributegroups.bigint_AttributeIncludedID > 0), 
			(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID), 
			NULL
		) AS text_AttributeParentDescription 
		SEPARATOR ', '), 
	3_serviceattributes.bigint_AttributeServiceID 
FROM 
	3_serviceattributes, 
	36_serviceattributegroups 
WHERE 
	3_serviceattributes.bigint_AttributeServiceID = 1 AND 
	3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID 
GROUP BY 
	36_serviceattributegroups.bigint_OwnerKeyID;

Open in new window

still gives the folllowing error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS text_AttributeParentDescription SEPARATOR ', '), 3_serviceattributes.big' at line 13
:/
0
 

Author Comment

by:intellisource
ID: 35203243
ah HAH!!!

:D took a break and did some excersizes, came back and got it working with the context as follows:
SELECT 
	36_serviceattributegroups.text_AttributeGroupDescription, 
	36_serviceattributegroups.bigint_OwnerKeyID, 
	3_serviceattributes.text_AttributeDescription, 
	GROUP_CONCAT(
		36_serviceattributegroups.bigint_AttributeIncludedID 
		SEPARATOR ', ') AS bigint_AttributeIncludedID, 
	GROUP_CONCAT(
		IF(
			(36_serviceattributegroups.bigint_AttributeIncludedID > 0), 
			(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID), 
			""
		) 
		SEPARATOR ', ') AS text_AttributeParentDescription, 
	3_serviceattributes.bigint_AttributeServiceID 
FROM 
	3_serviceattributes, 
	36_serviceattributegroups 
WHERE 
	3_serviceattributes.bigint_AttributeServiceID = 1 AND 
	3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID 
GROUP BY 
	36_serviceattributegroups.bigint_OwnerKeyID;

Open in new window

i guess a long day's development does take it out of you! lol :)
thanks again for your help in resolving this, angellll! :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question