I’m trying to imlplement a system of tags in a project of mine.

• Each ‘quote’ may have many tags, and each ‘tag’ may be used in a number of quotes

Thus, I have three tables, ‘quotes’, ‘tags’ and ‘tag_instances’.

I’m trying to make a query that returns details about a quote, including a string with all the tags in.

This was my first attempt at making it work:

SELECT
    quotes.*,
    GROUP_CONCAT(tags.title, ', ') AS quote_tags
FROM quotes
INNER JOIN tag_instances ON quotes.id = tag_instances.quote 
INNER JOIN tags ON tag_instances.tag = tags.id

However, this results in tag_list being a string concatenating all the tags with an instance. I’d rather just have the tags with an instance on the quote for that row.

I’m not particuarly familiar with SQLite, but I do know that in postgres, it’d be done like this:

SELECT 
    quotes.*
    STRING_AGG(tags.title, ', ') AS quote_tags
FROM quotes
INNER JOIN tag_instances ON quotes.id = tag_instances.quote
INNER JOIN tags ON tag_instances.tag = tag.id

Any advice translating the postgres flavoured sql to sqlite falvoured would be welcome.