Solved

i need to select grouped, but with the differing rows in group to be displayed as a semicolon or comma separated array string.

Posted on 2011-03-23
3
364 Views
Last Modified: 2012-05-11
hi therE,
i am selecting from the current two tables in MySQL 5.0.91-community:
TABLE 3_serviceattributes:
bigint_AttributeID 	text_AttributeDescription 	text_AttributeValue 	bigint_AttributeServiceID
1			Make				FORD			1
12			Model				AVALANCHE		1
40			Year				1997-2004		1
66			CabSize				REGULARCAB		1

TABLE 36_serviceattributegroups:
bigint_OwnerKeyID 	text_AttributeGroupDescription 	bigint_AttributeIncludedID
1			No Parents			0
12			Single Parent			1
40			Double Parents			1
40			Double Parents			12
66			Triple Parents			1
66			Triple Parents			12
66			Triple Parents			40

Open in new window

currently the sql query is as follows:
SELECT 
	36_serviceattributegroups.text_AttributeGroupDescription, 
	36_serviceattributegroups.bigint_OwnerKeyID, 
	3_serviceattributes.text_AttributeDescription, 
	36_serviceattributegroups.bigint_AttributeIncludedID, 
	IF(
		36_serviceattributegroups.bigint_AttributeIncludedID, 
		(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID), 
		""
	) 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;

Open in new window

this currently selects from the tables as follows:
text_AttributeGroupDescription	bigint_OwnerKeyID	
No Parents			1
Single Parent			12
Double Parents			40
Double Parents			40
Triple Parents			66
Triple Parents			66
Triple Parents			66

text_AttributeDescription	bigint_AttributeIncludedID
Make				0
Model				1
Year				1
Year				12
CabSize				1
CabSize				12
CabSize				40

text_AttributeParentDescription	bigint_AttributeServiceID
NULL				1
Make				1
Make				1
Model				1
Make				1
Model				1
Year				1

Open in new window

now i am wondering if it is at all possible to select grouping by bigint_OwnerKeyID or text_AttributeGroupDescription, the multiple differing values for bigint_AttributeIncludedID and text_AttributeParentDescription being imploded into a string array seperated by commas (,) or semicolons (;).
and if it is possible - how in THE hell would i get it right??? so as to only have 4 rows in the selection as follows:
text_AttributeGroupDescription	bigint_OwnerKeyID	
No Parents			1
Single Parent			12
Double Parents			40
Triple Parents			66

text_AttributeDescription	bigint_AttributeIncludedID
Make				0
Model				1
Year				1, 12
CabSize				1, 12, 40

text_AttributeParentDescription	bigint_AttributeServiceID
NULL				1
Make				1
Make, Model			1
Make, Model, Year		1

Open in new window

This has been giving me a headache all day - but it would be best if i could perform the select to output the last result mentioned - as i need to resolve this issue in a mysql select if at all possible. doing it in php would be too easy, and would be overkill afaik, especially keeping in mind possible future changes.
0
Comment
Question by:intellisource
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35201817
for the second part, you shall check out the GROUP_CONCAT() function...
0
 

Author Closing Comment

by:intellisource
ID: 35201855
thanks a million, angellll!!! XD
i was never even aware of this function - so let alone how to spell it :P heh
0
 

Author Comment

by:intellisource
ID: 35202127
ummm... stuck a bit - i do not understand how to use the GROUP_CONCAT function in the context i need to perform the crosstable select in. please refer to this post.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

759 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

19 Experts available now in Live!

Get 1:1 Help Now