SQLite: concatenate multiple columns across multiple tables

Question

I’d like to store struct-like data into a SQLite 3 database. Given is the following (sample) schema:

CREATE TABLE a(
    a_id        INTEGER PRIMARY KEY,
    name        TEXT
);

CREATE TABLE b(
    b_id        INTEGER PRIMARY KEY,
    a_id        INTEGER,
    name        TEXT,
    FOREIGN KEY (a_id) REFERENCES a(a_id)
);

CREATE TABLE c(
    c_id        INTEGER PRIMARY KEY,
    a_id        INTEGER,
    b_id        INTEGER,
    nclients    INTEGER,
    njobs       INTEGER,
    FOREIGN KEY (a_id) REFERENCES a(a_id),
    FOREIGN KEY (b_id) REFERENCES b(b_id)
);

Additionally, there a two more tables which reference c:

CREATE TABLE clients(
    client_id   INTEGER PRIMARY KEY,
    c_id        INTEGER,
    idx         INTEGER,
    name        TEXT,
    FOREIGN KEY (c_id) REFERENCES c(c_id)
);

CREATE TABLE jobs(
    job_id      INTEGER PRIMARY KEY,
    c_id        INTEGER,
    idx         INTEGER,
    task        TEXT,
    result      TEXT,
    FOREIGN KEY (c_id) REFERENCES c(c_id)
);

Adding some (sample) rows:

INSERT INTO a(name) VALUES ('AAA');
INSERT INTO b(a_id, name) VALUES (1, 'BBB');
INSERT INTO c(a_id, b_id, nclients, njobs) VALUES (1, 1, 2, 2);
INSERT INTO clients(c_id, idx, name) VALUES (1, 1, 'client1'), (1, 2, 'client2');
INSERT INTO jobs(c_id, idx, task, result) VALUES (1, 1, 'TASK1', 'RESULT1'), (1, 2, 'TASK2', 'RESULT2');

I’d like to select rows from c that also include columns from a, b, clients, and jobs. GROUP_CONCAT is probably not the right query, but to illustrate the desired output:

SELECT
    a.name AS a,
    b.name AS b,
    c.c_id AS c,
    c.nclients AS nclients,
    GROUP_CONCAT(clients.name) AS clients,                                    /* should be sorted by clients.idx */
    c.njobs AS njobs,
    GROUP_CONCAT(jobs.idx || ',' || jobs.task || ',' || jobs.result) AS jobs  /* should be sorted by jobs.idx */
FROM
    c
INNER JOIN a ON a.a_id = c.a_id
INNER JOIN b ON b.b_id = c.b_id
INNER JOIN clients ON clients.c_id = c.c_id
INNER JOIN jobs ON jobs.c_id = c.c_id
WHERE
    c.c_id = 1;

This query returns:

a    b    c  nclients  clients                          njobs  jobs                                                           
---  ---  -  --------  -------------------------------  -----  ---------------------------------------------------------------
AAA  BBB  1  2         client1,client1,client2,client2  2      1,TASK1,RESULT1,2,TASK2,RESULT2,1,TASK1,RESULT1,2,TASK2,RESULT2

First of all, there are duplicates in clients and jobs I don’t know by what they are caused. And futhermore, I’d like to sort clients and jobs by their respective idx column, preferably also limited to c.nclients/c.njobs.

How can I select the rows with these contrains?

Edit: fiddle

Answer 1

I am not sure if I understood correctly , but are you trying something like:

select tbl.a,tbl.b,tbl.c,tbl.nclients,GROUP_CONCAT(tbl.clients) AS clients,tbl.njobs,tbl.jobs
from (
SELECT  
  clients.name AS clients,
    a.name AS a,
    b.name AS b,
    c.c_id AS c,
    c.nclients AS nclients,    
    c.njobs AS njobs,
    jobs.idx as i,
    jobs.task as t,
    jobs.result as r,
    GROUP_CONCAT(jobs.idx  || ',' || jobs.task || ',' || jobs.result) AS jobs
FROM c
INNER JOIN a ON a.a_id = c.a_id
INNER JOIN b ON b.b_id = c.b_id
INNER JOIN clients ON clients.c_id = c.c_id
INNER JOIN jobs ON jobs.c_id = c.c_id
WHERE c.c_id = 1
 group by clients   ) as tbl ;

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/20