How can I export a list of values in a single field in MySQL?

Question

I want to join table A and B with B.c being a column that returns multiple strings as list. When I do an inner join with

select A.x, B.c
from A
join B
on A.y=B.y

I get an output with [number of values in list] rows per observation. Is there a way to write the list into a single field in the result table so I get a single row per observation?

UPDATE:

select profiles.name, profiles.street_no, profiles.street_name, profiles.postal_code, profiles.city, profiles.country_code, profiles.owner, profiles_emails.email, profiles_telephones.telephone, clusters.main_topic, group_concat(profiles_tags.tag)
from profiles
    join profiles_emails
        on profiles.id=profiles_emails.profiles_id 
    join profiles_telephones
        on profiles.id=profiles_telephones.profiles_id
    join profiles_tags
        on profiles.id=profiles_tags.profiles_id
    join profiles_clusters
        on profiles_clusters.profiles_id=profiles.id
            join clusters
                on profiles_clusters.clusters_id=clusters.id
group by profiles.city, profiles.name, profiles.street_no, profiles.street_name, profiles.postal_code, profiles.country_code, profiles.owner, profiles_emails.email, profiles_telephones.telephone, clusters.main_topic

The group by of course takes way too much time and the desired output is simply a table with all the selected columns and the last column as list.

Post some sample data and expected output. – Matt Mar 14 '17 at 15:10 — Matt, Mar 14 '17 at 15:10

score 1 · Accepted Answer · answered Mar 14 '17 at 15:15

1

seems you need group_concat .. assuming you have several b.c values for each a.x value you could use a group_concat eg:

  select A.x, group_concat(B.c)
  from A
  join B  on A.y=B.y
  group by a.x

answered Mar 14 '17 at 15:15

ScaisEdge

131,976
10
91
107

thanks, that helps. although I don't really understand why I need to group by every column I specify in the select, see also https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql Or do I misunderstand something? – Pete Mar 14 '17 at 17:04
if you update you question and add the code you are using i can eval you real problem .. and better you should add a real data sample and the expected result .. anyway of you are using mysql 5.7 you must add in group by all the column that are not in aggregation function (this is the correct way for group by) .. – ScaisEdge Mar 14 '17 at 17:07
Ok, did so. I think we are on the right track, now the query obviously takes forever with this many columns in the group by... – Pete Mar 14 '17 at 17:20
the cost for group by is not related to the column .. (eventually is related to the cardinality of the rows involved.. ) but for me if the query is slow this could be related to the join .. then be sure you have proper index on all column involved in join .. expecially for the bigger tables ...anyway .. is my answer right ? – ScaisEdge Mar 14 '17 at 17:26
before what ? ... ..before group by .. without .. aggregation function (group_concat) ?? .. how many rows you have in each tables .?. and have checked if the proper index are present ? .. – ScaisEdge Mar 14 '17 at 17:44
then tell me numbers of rows in all the tables and the index for all joins? – ScaisEdge Mar 14 '17 at 21:18
numbers of rows are about 200k per table. what do you mean by index for all joins? how can I find out? – Pete Mar 15 '17 at 08:46
i mean that you should have index for profiles_emails.profiles_id, ... profiles_telephones.profiles_id, ... profiles_tags.profiles_id .... profiles_clusters.profiles_id ... profiles_clusters.clusters_id .. or at least on the most populated .. – ScaisEdge Mar 15 '17 at 17:47

How can I export a list of values in a single field in MySQL?

1 Answers1