Pairing table query

I was wondering how I construct a query to get all articles with all their information, and for every article a list with their tags. I used a pairing table called ArticleTags to connect Article and Tags.

Can anyone tell me what the query would be if I wanted to receive all articles along with their tags per article.

My ERD:

I am using the Node module ‘mysql’.

Answer

You can use a correlated subquery:

SELECT article.*, (
    SELECT GROUP_CONCAT(tag.name ORDER BY tag.name SEPARATOR ', ')
    FROM articletag
    JOIN tag ON articletag.tagId = tag.id
    WHERE articletag.articleId = article.id
) AS tag_list
FROM article