CODEWITHSUNDEEP
sqlsqlite
Lucas
Lucas

Reputation: 2832

SQLite GROUP_CONCAT from another table, multiple joins

Having trouble with my sql query. Not an SQL expert by any means.

SELECT transactions.*, categories.*, GROUP_CONCAT(tags.tagName) as concatTags FROM transactions INNER JOIN categories ON transactions.category = categories.categoryId LEFT JOIN TransactionTagRelation AS ttr ON transactions.transactionId = ttr.transactionId LEFT JOIN tags ON tags.tagId = ttr.tagId;

(There's also a where and group by, but didn't think it was relevant to the question).

I'm trying to get: 

transactionId1, ...otherStuff..., "tagId1,tagId2,tagId3"  
transactionId2, ...otherStuff..., "tagId1,tagId3"

What I have now seems to merge the tags into one transaction or something. I tried adding a GROUP BY transactionID at the end, but it gives a syntax error for some reason. I have a feeling my joins are incorrect, but I wasn't able to get anything better.

Upvotes: 0

Views: 290

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Do something like this:

SELECT t.*, c.*,
       (SELECT GROUP_CONCAT(tg.tagName)
        FROM TransactionTagRelation ttr JOIN
             Tags tg
             ON tg.tagId = ttr.tagId
        WHERE t.transactionId = ttr.transactionId
       ) as concatTags
FROM transactions t JOIN
     categories c
     ON t.category = c.categoryId;

This eliminates the GROUP BY in the outer query and allows you to use t.* and c.* in the SELECT.

Upvotes: 1

Related Questions