select query with foreign keys in mysql

Question

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.

Do u want to retrieve the Articles without a Tag too? – Manuel Drieschmanns Aug 09 '17 at 11:13 — Manuel Drieschmanns, Aug 09 '17 at 11:13

juergen d · Accepted Answer · 2017-08-09T14:00:30.087

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

edited Aug 09 '17 at 14:00

answered Aug 09 '17 at 11:10

juergen d

201,996
37
293
362

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

select query with foreign keys in mysql

1 Answers1