postgresql - 如何在多个一对多关系中加入没有额外重复的表

Question

我有两个与父表具有一对多关系的子表。我想加入他们而不需要额外的重复。

在实际模式中，与此父表和子表之间存在更多一对多关系。我正在共享一个简化的架构，以使问题的根源更容易被发现。

非常感谢任何建议。

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');

这是我的查询:

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

我得到这个结果:

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"]

我想得到这个结果(通过保留重复项，如果数据库中存在的话)。并且还能够按用户和/或附件过滤计算机:

1 "HP" ["John", "Elton", "David"] ["keyboard", "mouse", "mouse"]
2 "Toshiba" ["Ali"] ["monitor", "printer", "mouse"]
3 "Dell" Null ["speaker"]

Answer 1

使用子查询而不是连接:

SELECT 
    c.id,
    c.name,
    (SELECT
        jsonb_agg(c_user.name)
        FROM c_user
        WHERE c_user.computer_id = c.id
    ) AS user_names,
    (SELECT
        jsonb_agg(c_accessories.name)
        FROM c_accessories
        WHERE c_accessories.computer_id = c.id
    ) AS accessory_names
FROM 
    computer c