I have a database where the first table is an "article" table(id, body...) and a second one containing words (word_id, word). These words are like labels/topics of the articles

another table connects the previous ones: article_labels(article_id, word_id).

some articles don't have labels(not included in article_labels table) and the majority of articles have multiple labels.

what I have is a query to get just the labeled articles ( id | body | label1/label2/...)

   select  article_id, body
           group_concat(word SEPARATOR '/') AS labels 
   from article_labels l,
        portfolio_2022.words w,
        articles a
   where a.language='en'
     and l.word_id = w.id
     and a.id = l.article_id 
   group by article_id;

what I want to do is to get all the articles with their labels, if an article is not labeled a default value(e.g "unlabeled") is inserted.

Thank you in advance!

CodePudding user response：

You can use a sub query for this:

select id, body, coalesce((
    select group_concat(words.word separator '/')
    from article_labels
    join words on article_labels.word_id = words.id
    where article_labels.article_id = articles.id 
), 'unlabeled') as labels
from articles
where language = 'en'