CODEWITHSUNDEEP
mysqlsql
Ruben
Ruben

Reputation: 5

Get records in Many to Many query

I have three tables: 1.article - 2.tag - 3.article_has_tags (An article may have many tags).

article:

id  | title 
1   |  A1   
2   |  A2  
3   |  A3

tag:

id  | title 
1   |  T1   
2   |  T2  
3   |  T3

article_has_tags: (it contents article & tag primary keys)

aid | tid 
 1  |  1   
 1  |  2  
 1  |  3
 2  |  1
 3  |  2

I need to return the first three articles with all their tags.

My query is:

SELECT article.id, article.title, tag.title FROM article
JOIN article_has_tags ON (article.id = article_has_tags.aid)
JOIN tag ON (article_has_tags.tid = tag.id) ORDER BY article.id limit 3

But obviously it's returning:

article.id | article.title | tag.title
    1      |        A1     |    T1
    1      |        A1     |    T2
    1      |        A1     |    T3

The result I need is something like this:

article.id | article.title | tag.title
    1      |        A1     | T1-T2-T3
    2      |        A2     |    T1
    3      |        A3     |    T2

How can I get the result I need? Thank you.

Upvotes: 0

Views: 44

Answers (1)

zealous
zealous

Reputation: 7503

you can use group_concat in MySQL. Here is the demo.

SELECT 
    article.id, 
    article.title, 
    group_concat(tag.title SEPARATOR '-') as tag
FROM article
JOIN article_has_tags 
ON (article.id = article_has_tags.aid)
JOIN tag 
ON (article_has_tags.tid = tag.id) 
GROUP BY
    article.id, 
    article.title
ORDER BY 
    article.id

Output:

*-------------------*
| id  title   tag   |
*-------------------*
  1    A1   T3-T1-T2
  2    A2     T1
  3    A3     T2
*-------------------*

Upvotes: 1

Related Questions