CODEWITHSUNDEEP
mysqlsql
user1477388
user1477388

Reputation: 21440

SQL Concatenate Joined Results into Column

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

Upvotes: 0

Views: 137

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 2

Related Questions