Joining 1 MySQL row with several results

I have a problem listing articles and their respective tags, maybe someone can help me.

Imagine the query:

select *.articles, tags.tag_name 

from articles

left join tags on tags.article_id = articles.article_id

where articles.article_id = 1

This would work fine if the article only had one tag. The problem is if the articles has more than 1 tag, then I get 1 row per tag for the same article. If I want to show these articles in a for each it shows to many of the same article.

Is there a way to keep this in one row or do I need to use two queries, one for the article info开发者_开发问答 and one for the tags? This would be bad since a list of 50 artciles would require 50 queries to show their tags.

If an article can have more than one tag you have to create an intermediate table which will contain id of articles and tags, so you will be able to link many articles with many tags.

Then your query will be

select *.articles, tags.tag_name 

from articles

inner join rel_tags on articles.article_id = rel_tags.article and articles.article_id = 1

inner join tags on rel_tags.tag = tags.tag_id

You could use GROUP_CONCAT which by default returns a comma seperated list of values over your grouped expression. eg.

select a.article_id, GROUP_CONCAT(t.tag_name)
from articles a
    left join tags t using (article_id)
where a.article_id = 1
group by a.article_id

Or run the one query with the join and get duplicated article information, which you can then re-combine programatically. This can be automated by using an ORM.

You can use the GROUP_CONCAT() function:

SELECT *.articles, GROUP_CONCAT(tags.tag_name)
FROM articles
LEFT JOIN tags ON tags.article_id = articles.article_ID
WHERE articles.article_id = 1
GROUP BY articles.article_id

It'll convert those extra rows into a single field.