• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

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

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
intellisource
Asked:
intellisource
  • 5
1 Solution
 
intellisourceAuthor Commented:
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
 
intellisourceAuthor Commented:
lol damnit - i meant this post.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
intellisourceAuthor Commented:
oki thankyouuuu! XD
been an awesome lot of help since my tired mind does not want to go further :P
0
 
intellisourceAuthor Commented:
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
 
intellisourceAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now