All geek questions in one place

PostgreSQL - How to connect to the M: M table correctly?

My database structure is as follows:

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

What I'm trying to achieve is to list all the drinks and their attributes ( price, descriptionetc.) and add another column additivesfrom the table b_additives_xrefthat contains a concatenated row with all the additives contained in each drink.

Currently my query looks like this and works (I think):

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;

The result is as follows:

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)[...]

Which, of course, is not true, since only Coca Cola has existing rows in the table b_additives_xref.
With the exception of the Coca Cola row, all other rows must be null or blank in the column append. What am I doing wrong?

+4
3

,

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

. xref.

+1

CREATE TABLE category (
   category_id int PRIMARY KEY
  ,category    text UNIQUE NOT NULL
);

CREATE TABLE beverage (
   beverage_id serial PRIMARY KEY
  ,beverage    text UNIQUE NOT NULL  -- maybe not unique?
  ,description text
  ,price       int NOT NULL CHECK (price >= 0)  -- in Cent
  ,category_id int NOT NULL REFERENCES category ON UPDATE CASCADE
                                        -- not: ON DELETE CASCADE 
);

CREATE TABLE additive (
   additive_id serial PRIMARY KEY
  ,additive    text UNIQUE
);

CREATE TABLE bev_add (
    beverage_id int REFERENCES beverage ON DELETE CASCADE ON UPDATE CASCADE
   ,additive_id int REFERENCES additive ON DELETE CASCADE ON UPDATE CASCADE 
   ,PRIMARY KEY(beverage_id, additive_id)
);

:
" " PostgreSQL?

Query

.

SELECT b.*, string_agg(a.additive, ', ' ORDER BY a.additive) AS additives
                                     -- order by optional for sorted list
FROM   beverage      b
JOIN   category      c USING (category_id)
LEFT   JOIN bev_add ba USING (beverage_id)  -- simpler now
LEFT   JOIN additive a USING (additive_id)
GROUP  BY b.beverage_id, c.category_id
ORDER  BY c.category;
  • , .
  • USING .
  • category GROUP BY category_id category ( ).
  • - , , , .
+2

, , :

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;

, .

+1

Source: https://habr.com/ru/post/1540301/

More articles:


All Articles