Solved

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

Posted on 2011-03-23
6
795 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

863 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