Solved

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

Posted on 2011-03-23
6
792 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
  • 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now