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
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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 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 …

636 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