GROUP CONCATing multiple rows

Question

I'm using MySQL 5.1.66 and I came up with a select query to select all the men's shirts and their sizes and prices. I used GROUP CONCAT to merge the size column and price column from their parent tables into the same column. Here's the statement I made

SELECT shirts.shirt_name, shirts.men AS main_photo, GROUP_CONCAT(shirt_sizes.size_name,shirt_prices.price) AS sizes FROM shirts JOIN shirts_link ON shirts_link.shirt_id=shirts.id JOIN shirt_sizes ON shirt_sizes.id=shirts_link.size_id JOIN shirt_prices ON shirt_prices.id=shirts_link.price_id WHERE shirts.men!='' AND shirts_link.adult='y' GROUP BY shirts.id

By doing this, are the size columns and price columns still two separate entities within that one column in the select statement? Another thing I noticed is the order of the sizes is all mixed up. For example, this is a row from one of the columns

medium29.22,large29.22,1x-large29.22,2x-large30.44,small29.22,3x-large31.70

why isn't it going from small-3x like it's organized on the table? I imagine in the event of what I'm trying to do in terms of injecting it into we website through PHP if it were to auto load it would go in that unorganized manner as well. How can I fix this?

My ultimate goal here is to be able to create a selection that can auto populate each row into the javascript apps I created. I need the load the shirt name, main picture, then each size and price into the divs I have coded for them. Let me know if you need to see the actual tables being used. Thanks in advance :)

ok I just updated it to show that, and I'm using MySQL 5.1.66. — Optiq
– Optiq, Commented Dec 31, 2012 at 11:00

Mariappan Subramanian · Accepted Answer · 2012-12-31 10:58:43Z

First of all purpose of group_concat function is to keep all values of a particular column in a grouped operation. For example,

Table1 ID field1 field2 1 ram science 2 ramesh maths 1 ram maths

If your requirement is to get subjects of a particular person then GROUP_CONCAT will produce then result as follows,

ID field1 field3 1 ram science,maths 2 ramesh maths

Hope this gives some insight

I understand that part of it. What I'm trying to figure out is if we do that same thing with 2 columns like I did can they still be called upon from that cell individually by referencing the column names. Hope this helps some.
yeah they can be called. Ur only problem is they are not coming in proper order as tables??

Saharsh Shah · Accepted Answer · 2012-12-31 12:55:20Z

Try this:

SELECT s.shirt_name, s.men AS main_photo, GROUP_CONCAT(CONCAT(ss.size_name, '.', sp.price)) AS sizes FROM shirts s INNER JOIN shirts_link sl ON sl.shirt_id = s.id INNER JOIN shirt_sizes ss ON ss.id = sl.size_id INNER JOIN shirt_prices sp ON sp.id = sl.price_id WHERE s.men!='' AND sl.adult='y' GROUP BY s.id

Collectives™ on Stack Overflow

GROUP CONCATing multiple rows

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related