Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
370 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, we’ll look at how to deploy ProxySQL.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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