I have a three table schema (see image above) Articles, Tags and ArticleTags. The ArticleTags table maps article_id to a tag_id. Im trying to retrieve all rows from Articles and join the tags associated with each row.
Asked
Active
Viewed 85 times
0
-
1SO is not a free coding service. You have to make some attempt to solve the problem yourself. If you can't get it working, post what you tried and we'll help you fix it. – Barmar Aug 09 '17 at 11:11
-
Do u want to retrieve the Articles without a Tag too? – Manuel Drieschmanns Aug 09 '17 at 11:13
1 Answers
1
select a.article_id, group_concat(t.tag_name) as tag_names
from articles a
left join ArticleTags at on at.article_id = a.article_id
left join tags t on at.tag_id = t.tag_id
group by a.article_id
juergen d
- 195,137
- 36
- 275
- 343
-
I forgot to add the query i'd been trying. similar to what you have. This query results in duplication of rows. I was trying a group_concat(a.article_id). Essentially I'm looking for an array of tags for each article_id. ``select a.*, t.tag_name, group_concat(a.article_id) from articles a left join ArticleTags at on at.article_id = a.article_id left join tags t on at.tag_id = t.tag_id`` – Anirvan Awatramani Aug 09 '17 at 13:51
-
worked like a charm! I was about to make two separate queries and manipulate the outputs in JS. – Anirvan Awatramani Aug 09 '17 at 16:24