mysql join with multiple values in one column

Question

I need to make a query that creates 3 columns that come from 2 tables which have the following relations:

TABLE 1 has Column ID that relates to TABLE 2 with column ID2

In TABLE 1 there is a column called user In TABLE 2 there is a column called names

There can be 1 unique user but there can be many names associated to that user.

If i do the following i get all data BUT the user column repeats itself for each name it has associated. What i want is for use to appear unique but the names columns appear with all the names associated to the user column but separated by commas, like the following:

select user,names from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id

This will show the users repeated everytime a name appears for that user. what i want is to appear like this:

USER - NAMES
cyrex - pedrox, rambo, zelda
homeboy - carmen, carlos, tom, sandra
jerry - seinfeld, christine
ninja - soloboy

etc....

Eric Petroelje · Accepted Answer · 2011-01-05 22:23:57Z

18

What you are looking for is the GROUP_CONCAT operator.

select user, GROUP_CONCAT(names SEPARATOR ',')
from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id
group by user

answered Jan 5, 2011 at 22:23

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql join with multiple values in one column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related