asked on

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. -_-

intellisource

ASKER

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?

intellisource

ASKER

lol damnit - i meant this post.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

intellisource

ASKER

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

intellisource

ASKER

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

intellisource

ASKER

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! :)