How do I return all results of an inner join when filtering by an attribute of a joined table

Question

I have a SQL query that filters logs by what kind of tags they have. Sometimes the logs have multiple tags. The issue is that when I filter by a tag, the returned logs only include the tag that I've filtered with. When filtering by a tag, I would like to get all logs with that tag, with all of their tags.

i.e. When filtering by "a pink tag" I would like to have...

But instead I'm getting this...

Below is my SQL query

        var logsQuery = `PREPARE LogQuery (text) AS\
            SELECT trackerlogs.*,\
            trackers.serialnumber,\
            trackers.active,\
            trackers.actdate,\
            trackers.deactdate,\
            trackerclaims.devicename,\
            trackerclaims.claimtype,\
            t.id AS tagid,\
            t.tagname,\
            t.color\
            FROM trackerlogs INNER JOIN\
            trackers ON trackerlogs.deviceid = trackers.id\
            INNER JOIN trackerclaims ON trackerclaims.deviceid = trackers.id\
            LEFT JOIN\
            (SELECT tags.*, trlogtags.logid AS logid from tags\
            INNER JOIN trlogtags ON trlogtags.tagid = tags.id\
            ${tags.length > 0 ? `AND t.id IN (${tags.join()})` : ""}\
            EXECUTE LogQuery('${search === "" ? "%" : "%" + search + "%"}');`;

I believe the relevant part of the query is:

${tags.length > 0 ? `AND t.id IN (${tags.join()})` : ""}\

However every time I try to change this, I'm not able to filter by tags anymore.

Any help is greatly appreciated. Please let me know if the explanation isn't clear, additional context is needed, or if the answer is somewhere else on StackOverflow and I'm blind.

Answer 1

Maybe you'll need to this in 2 steps:

1. Get all the logs that have the tag that you'll need
2. Join all tags from the logs from step 1

Something like this:

with selected_logs as (
    select
        l.id as log_id
    from logs l
    join tags t on t.log_id = l.id
        and t.tag_name = '{{filter}}'
)
select 
    l.*,
    t.*
from selected_logs sl
    join logs l on sl.log_id = l.id
    join tags t on t.log_id = sl.log_id