MySql Query - Joining

Question

I have 2 tables:

articles
id int auto_increment
title VARCHAR
desc VARCHAR
etc

tags
id int auto_increment
articleId int
tag Varchar

The idea is that articles have multiple tags.

I want to select an article with all of it's tags in a single field separated by a comma or something.

The result would look like:

title    |   desc    |      tags       |
---------------------------------------
article1 |   desc1   | Tech,Science    |
article2 |   desc2   | Drama,Tv,Funny  |

I'm looking for help with the query to do this.

Here's what I have... I know it's not right... I'm guessing I need some kind of join and concatenation?

SELECT * 
FROM portfolio.articles, portfolio.tags 
WHERE articles.id = tags.articleId;

Any help would be great!

Answer 1

you need to use the group_concat function

so you would have something like this:

select
a.title,
a.desc,
group_concat(t.tag) 

from articles a
inner join tags t on a.id = t.articleId

group by 
a.title, a.desc

this will seperate each tag with a comma

Answer 2

Try:

SELECT a.title, a.desc, group_concat(t.tag) as tags
  FROM portfolio.articles a
  join portfolio.tags t
    on a.id = t.articleId
 group by a.title, a.desc

You want to use the group_concat function, which is basically vertical concatenation.