0

I have two tables:

Table1:
id (uniqueidentifier, primarykey)
title (varchar(50))

Table2:
id (uniqueidentifier, primarykey)
table1id (uniqueidentifier, foreignkey to table1)
category (varchar(50))

I also have the following SQL to return to me all results from Table1 and all their respective categories from Table2.

select t1.*, t2.category as cat from table1 as t1
left join table2 as t2 on t1.id = t2.table1id

The problem is, there could be multiple results for category, so how can I concatenate them by comma into the column for cat?

For example, Table2 could contain the following data:

Table2 row 1:
id = 1
table1id = 1
category = "abc"

Table2 row 2:
id = 2
table1id = 1
category = "def"

See how the two records have the same table1id but different values for category.

How can I concatenate both (or more) potential values by comma and return it as a single string to the resulting column cat from the query above?

Desired output:
t1.id = 1
t1.title = table1 title
cat = abc, def
user1477388
  • 20,790
  • 32
  • 144
  • 264
  • possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) – M Khalid Junaid Aug 23 '14 at 17:25

1 Answers1

2

Use group_concat on t2.category and group by the other columns you want to select.

select t1.id, t1.title, group_concat(t2.category) as cat from table1 as t1
left join table2 as t2 on t1.id = t2.table1id
group by t1.id, t1.title
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • Thanks but it doesn't seem to be grouping (i.e. it's not removing duplicates). For example, it is showing `IE8122014-PHL6,IE8122014-PHL6,IE8122014-PHL6`. How can I remove these duplicates and only show the category once per row? – user1477388 Aug 23 '14 at 17:37
  • **Edit:** I tried `group_concat(DISTINCT t2.category)` and it works fine per http://stackoverflow.com/questions/4561650/mysql-group-concat-duplicates – user1477388 Aug 23 '14 at 17:39