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!

Brian DeMilia · Accepted Answer · 2014-08-02 19:44:10Z

1

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.

answered Aug 2, 2014 at 19:44

Brian DeMilia

13.3k1 gold badge25 silver badges36 bronze badges

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

4 Comments

Gordon Linoff Over a year ago

+1 for being first . . . and not by a subtle amount. 4.5 minutes seems like a long time to type an answer.

Brian DeMilia Over a year ago

@GordonLinoff thanks... site can be bizarre sometimes

Gordon Linoff Over a year ago

When you hover over the time, you can see the exact time stamp. I learned this from Aaron Bertrand, once upon a time. He was first, but at least in that case the difference would have been in seconds, not minutes.

Brian DeMilia Over a year ago

Ah, I'll have to remember that. It should probably just show it underneath the 'x hrs/mins ago' indicator, and/or order the posts in ascending order of time beneath the question.

ShavedWheels · Accepted Answer · 2014-08-02 19:48:39Z

0

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

answered Aug 2, 2014 at 19:48

ShavedWheels

345 bronze badges

1 Comment

ShavedWheels Over a year ago

when I started typing there were no answers. Both are correct.

Collectives™ on Stack Overflow

MySql Query - Joining

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related