Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE computer (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE c_user (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

CREATE TABLE c_accessories (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

INSERT INTO computer (name) VALUES ('HP'), ('Toshiba'), ('Dell');
INSERT INTO c_user (computer_id, name) VALUES (1, 'John'), (1, 'Elton'), (1, 'David'), (2, 'Ali');
INSERT INTO c_accessories (computer_id, name) VALUES (1, 'mouse'), (1, 'keyboard'), (1, 'mouse'), (2, 'mouse'), (2, 'printer'), (2, 'monitor'), (3, 'speaker');

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 4



INSERT 0 7


SELECT 
    c.id
    ,c.name
    ,jsonb_agg(c_user.name)
    ,jsonb_agg(c_accessories.name)
FROM 
    computer c
JOIN 
    c_user ON c_user.computer_id = c.id
JOIN 
    c_accessories ON c_accessories.computer_id = c.id
GROUP BY c.id


id
name
jsonb_agg
jsonb_agg


1
HP
["John", "John", "John", "Elton", "Elton", "Elton", "David", "David", "David"]
["mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse"]

2
Toshiba
["Ali", "Ali", "Ali"]
["monitor", "printer", "mouse"]



SELECT 2


SELECT ca.id,
       jsonb_agg(u.name) AS users,
       ca.accessories
       FROM (SELECT c.id,
                    jsonb_agg(a.name) AS accessories
                    FROM computer AS c
                         LEFT JOIN c_accessories AS a
                                   ON a.computer_id = c.id
                    GROUP BY c.id) AS ca
            INNER JOIN c_user AS u
                       ON u.computer_id = ca.id
       GROUP BY ca.id,
                ca.accessories;


id
users
accessories


1
["John", "Elton", "David"]
["mouse", "keyboard", "mouse"]

2
["Ali"]
["mouse", "printer", "monitor"]



SELECT 2


SELECT c.id,
       (SELECT jsonb_agg(x.name)
               FROM unnest(array_agg(DISTINCT row(u.id, u.name))) AS x
                                                                     (id integer,
                                                                      name text)) AS users,
       (SELECT jsonb_agg(x.name)
               FROM unnest(array_agg(DISTINCT row(a.id, a.name))) AS x
                                                                     (id integer,
                                                                      name text)) AS accessories
       FROM computer AS c
            LEFT JOIN c_accessories AS a
                      ON a.computer_id = c.id
            INNER JOIN c_user AS u
                       ON u.computer_id = c.id
       GROUP BY c.id;


id
users
accessories


1
["John", "Elton", "David"]
["mouse", "keyboard", "mouse"]

2
["Ali"]
["mouse", "printer", "monitor"]



SELECT 2

id	name	jsonb_agg	jsonb_agg
1	HP	["John", "John", "John", "Elton", "Elton", "Elton", "David", "David", "David"]	["mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse"]
2	Toshiba	["Ali", "Ali", "Ali"]	["monitor", "printer", "mouse"]

id	users	accessories
1	["John", "Elton", "David"]	["mouse", "keyboard", "mouse"]
2	["Ali"]	["mouse", "printer", "monitor"]