I need to link the article tables and tags. I created 3 tables
create table Articles (
Id serial primary key,
Title char(64),
Descriptions text
);
create table Tags (
Id serial primary key,
Name char(64)
);
create table ArticlesTags (
ArticleId integer not null references Articles(Id),
TagId integer not null references Tags(Id)
);
How can I now correctly formulate a sql-request to receive an article and all the tags to it?
Join the three tables:
SELECT a.title,
array_agg(t.name) FILTER (WHERE t.name IS NOT NULL) AS tags
FROM articles a
LEFT JOIN articlestags at ON a.id = at.articleid
LEFT JOIN tags t ON at.tagid = t.id
WHERE a.title = 'whatever'
GROUP BY a.title;
As a slight variation on the answer by @Laurenz (+1), we can use left joins in case a given article might not even have any tags listed:
SELECT a.Title, COALESCE(t.Name, 'NA') AS tag_name
FROM Articles a
LEFT JOIN ArticlesTags at
ON a.Id = at.ArticleId
LEFT JOIN Tags t
ON at.TagId = t.Id
WHERE
a.Title = 'some title';
