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
365 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

Zoho SalesIQ

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

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

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

18 Experts available now in Live!

Get 1:1 Help Now