SQLite select subquery return multiple rows as a list in column-CodePudding

So I've redesigned my database after I realized it can be made better by splitting previous columns that were "lists" (a string of words/strings split by a space), into different tables instead.

My issue is that I need to get the file names from one table that have a certain uid that references a uid in another table, I have done this mostly, except the fact that I need to be able to get multiple rows from the original table that references the second table.

If this didn't make sense, here is my code and relevant structure:

SELECT * FROM (
    SELECT 
        a.uid, a.reward_title, a.reward_content, 
        a.reward_date as date_time, a.reward_pinned as pinned, (
            SELECT c.file_name FROM files c WHERE c.reference_uid = a.uid AND c.reference_type = ?
        ) as images
    FROM rewards a
    INNER JOIN subscriptions b ON b.creator_uid = a.creator_uid AND b.tier_hierarchy = a.tier_hierarchy
    WHERE b.user_uid = ?
)

CREATE TABLE rewards (
    uid TEXT UNIQUE PRIMARY KEY NOT NULL,
    creator_uid TEXT NOT NULL,
    tier_hierarchy INTEGER,
    reward_title TEXT NOT NULL,
    reward_content TEXT NOT NULL,
    reward_date TEXT NOT NULL,
    reward_tags TEXT,
    reward_pinned INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (creator_uid) REFERENCES users (uid)
)

CREATE TABLE files (
    uid TEXT UNIQUE PRIMARY KEY NOT NULL,
    reference_uid TEXT NOT NULL,
    reference_type TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_type INTEGER NOT NULL DEFAULT 1,
    thumbnail BOOLEAN DEFAULT NULL
)

files table

rewards table

Currently this returns this

As you can see in the second image, there are two rows that from the conditions would return, I want to know how I can get both of those rows file_name column into a list as the 6th column in the original select

CodePudding user response：

You use group_concat() to collapse of multiple rows into a single row.