sql - PostgreSQL-如何以正确的方式联接M:M表？

我的数据库结构如下：

CREATE TABLE categories (
    name VARCHAR(30) PRIMARY KEY
);

CREATE TABLE additives (
    name VARCHAR(30) PRIMARY KEY
);

CREATE TABLE beverages (
    name VARCHAR(30) PRIMARY KEY,
    description VARCHAR(200),
    price NUMERIC(5, 2) NOT NULL CHECK (price >= 0),
    category VARCHAR(30) NOT NULL REFERENCES categories(name) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE b_additives_xref (
    bname VARCHAR(30) REFERENCES beverages(name) ON DELETE CASCADE ON UPDATE CASCADE,
    aname VARCHAR(30) REFERENCES additives(name) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(bname, aname)
);


INSERT INTO categories VALUES
    ('Cocktails'), ('Biere'), ('Alkoholfreies');

INSERT INTO additives VALUES
    ('Kaliumphosphat (E 340)'), ('Pektin (E 440)'), ('Citronensäure (E 330)');

INSERT INTO beverages VALUES
    ('Mojito Speciale', 'Cocktail mit Rum, Rohrzucker und Minze', 8, 'Cocktails'),
    ('Franziskaner Weißbier', 'Köstlich mildes Hefeweizen', 6, 'Biere'),
    ('Augustiner Hell', 'Frisch gekühlt vom Fass', 5, 'Biere'),
    ('Coca Cola', 'Coffeeinhaltiges Erfrischungsgetränk', 2.75, 'Alkoholfreies'),
    ('Sprite', 'Erfrischende Zitronenlimonade', 2.50, 'Alkoholfreies'),
    ('Karaffe Wasser', 'Kaltes, gashaltiges Wasser', 6.50, 'Alkoholfreies');

INSERT INTO b_additives_xref VALUES
    ('Coca Cola', 'Kaliumphosphat (E 340)'),
    ('Coca Cola', 'Pektin (E 440)'),
    ('Coca Cola', 'Citronensäure (E 330)');

SqlFiddle
我要实现的是列出所有饮料及其属性（price，description等），并在additives表中添加另一列b_additives_xref，该列包含一个串联字符串，其中包含每个饮料中包含的所有添加剂。
我的查询当前看起来是这样的，并且几乎正在工作（我猜）：

SELECT
    beverages.name AS name,
    beverages.description AS description,
    beverages.price AS price,
    beverages.category AS category,
    string_agg(additives.name, ', ') AS additives
FROM beverages, additives
    LEFT JOIN b_additives_xref ON b_additives_xref.aname = additives.name
GROUP BY beverages.name
ORDER BY beverages.category;

输出如下：

Coca Cola       | Coffeeinhaltiges Erfrischungsgetränk | 2.75 | Alkoholfreies | Kaliumphosphat (E 340), Pektin (E 440), Citronensäure (E 330)
Karaffe Wasser  | Kaltes, gashaltiges Wasser           | 6.50 | Alkoholfreies | Kaliumphosphat (E 340), Pektin (E 440), Citronensäure (E 330)
Sprite          | Erfrischende Zitronenlimonade        | 2.50 | Alkoholfreies | Kaliumphosphat (E 340), Pektin (E 440), Citronensäure (E 330)
Augustiner Hell | Frisch gekühlt vom Fass              | 5.00 | Biere         | Kaliumphosphat (E 340)[...]

当然，这是错误的，因为只有'Coca-Cola’在b_additives_xref表中存在行。
除了“Coca-Cola”行之外，所有其他行的“additives”列中都应该有“null”或“empty field”值。我做错什么了？

最佳答案

我相信你在找这个

SELECT
    B.name AS name,
    B.description AS description,
    B.price AS price,
    B.category AS category,
    string_agg(A.name, ', ') AS additives
FROM Beverages B
    LEFT JOIN b_additives_xref xref ON xref.bname = B.name
    Left join additives A on A.name = xref.aname
GROUP BY B.name
ORDER BY B.category;

输出

NAME    DESCRIPTION                                 PRICE   CATEGORY        ADDITIVES
Coca Cola   Coffeeinhaltiges Erfrischungsgetränk    2.75    Alkoholfreies   Kaliumphosphat (E 340), Pektin (E 440), Citronensäure (E 330)

问题是在beverages和additives表之间有一个笛卡尔积

FROM beverages, additives

每个唱片都有其他唱片的位置。它们都需要显式地连接到外部参照表。