Hilfe bei Query vermutlich mit CTE rekursiv

G

Grille

Neuer Benutzer
Beiträge
2
Hallo Leute,

ich stehe gerade auf dem Schlauch, bei einer zunächst einfach aussehenden Aufgabe ...

Diese Tabellen dienen zum besseren Verständnis, passen jedoch vom Aufbau zu den Tabellen eines vorhandenen Programms.
Eine Anpassung der Tabellen kann ich somit leider nicht vornehmen, da diese durch eine andere Software erstellt und beschrieben werden.
Es gibt also tatsächlich keine Constraints, IDs, ....


Kurz zur Beschreibung der Quellen und des erwarteten Ziels:

Ziel:
Eine Einkaufsliste, bei welcher sämtliche dafür notwendige Zutaten dem Grundrezept zugeordnet werden.

1740144656819.webp

Quellen:
- Bestellung - enthält alle Bestellungen in entsprechender Anzahl. Diese können mehrfach vorhanden sein, aber lassen sich ja durch Gruppierung summieren.
- Rezepte - mit den Haupt- und Unterrezepten, zu welchen die jeweiligen Zutaten(Artikeln) zugeordnet sind.
- Artikelliste - in welcher beschrieben ist, ob der Artikel eingekauft werden kann (Einkaufen), oder hergestellt werden muss (Rezept).

SQL: 
CREATE TABLE rezepte (

    rezept VARCHAR(20) NOT NULL,
    artikel VARCHAR(20)  NOT NULL,
    menge NUMERIC(4,2) NOT NULL,
    einheit VARCHAR(4) NOT NULL,
    UNIQUE (rezept, artikel, menge, einheit)
)

CREATE TABLE artikelliste (
    artikel VARCHAR(20) NOT NULL,
    typ VARCHAR(10) NOT NULL,
    UNIQUE (artikel)
)

CREATE TABLE bestellung (
    artikel VARCHAR(20) NOT NULL,
    menge integer NOT NULL
)

INSERT INTO artikelliste
    (artikel, typ)
 VALUES
 ('Zitronenkuchen',    'Rezept'),
 ('Schokokuchen',    'Rezept'),
 ('Hefeteig',    'Rezept'),
    ('Dekoperlen',    'Einkaufen'),
    ('Zitronenglasur',    'Rezept'),
    ('Zitrone',    'Einkaufen'),
    ('Boden', 'Einkaufen'),
    ('Zitronensaft', 'Einkaufen'),
    ('Zucker', 'Einkaufen'),
    ('Ei', 'Einkaufen'),
    ('Wasser', 'Einkaufen'),
    ('Backpulver', 'Einkaufen'),
    ('Mehl', 'Einkaufen'),
    ('Hefe', 'Einkaufen'),
    ('Salz', 'Einkaufen'),
    ('Puderzucker', 'Rezept'),
    ('Schokomasse', 'Rezept'),
    ('Eiweiß', 'Rezept'),
    ('Eigelb', 'Rezept'),
    ('Eischnee', 'Rezept'),
    ('Eischaum', 'Rezept');

INSERT INTO rezepte
    (rezept, artikel, menge, einheit)
 VALUES
 ('Zitronenkuchen', 'Dekoperlen', '1', 'STK'),
 ('Zitronenkuchen', 'Zitronenglasur', '1', 'STK'),
    ('Zitronensaft', 'Zitrone', '1', 'ml'),
    ('Zitronenkuchen', 'Boden', '1', 'STK'),
    ('Zitronenglasur', 'Zitronensaft', '50', 'ml'),
    ('Zitronenglasur', 'Puderzucker', '250', 'g'),
    ('Schokoladenkuchen', 'Schokomasse', '1', 'STK'),
    ('Schokomasse', 'Ei', '4', 'STK'),
    ('Puderzucker', 'Zucker', '1', 'g'),
    ('Eiweiß', 'Ei', '1', 'STK'),
    ('Eischnee', 'Eiweiß', '4', 'STK'),
    ('Eischaum', 'Eigelb', '4', 'STK'),
    ('Eischaum', 'Wasser', '2', 'EL'),
    ('Eischaum', 'Zucker', '200', 'g'),
    ('Eigelb', 'Ei', '1', 'STK'),
    ('Boden', 'Eischnee', '1', 'STK'),
    ('Boden', 'Eischaum', '1', 'STK'),
    ('Boden', 'Backpulver', '1.5', 'TL'),
    ('Boden', 'Mehl', '200', 'g'),
    ('Hefeteig', 'Hefe', '5', 'g'),
    ('Hefeteig', 'Mehl', '500', 'g'),
    ('Hefeteig', 'Wasser', '300', 'ml'),
    ('Hefeteig', 'Salz', '2', 'TL');

INSERT INTO bestellung
    (artikel, menge)
    VALUES
    ('Schokoladenkuchen',1),
    ('Zitronenkuchen',2),
    ('Zitronenkuchen',1),
    ('Hefeteig',2);

In meinem Ansatz habe ich zunächst die beiden Tabellen Rezepte und Artikelliste mit einem Join zu RezeptArtikelTypen verbunden.

Die Tabelle RezeptArtikelTypen ist mit sich selbst verbunden zu RezeptEinkaufsArtikel.
Dieser Schritt muss vermutlich rekursiv sein, wa sich aber leider nicht hinbekomme....

Meine Anfrage bezieht sich auf die Tabelle RezeptArtikelTypen



SQL: 
SELECT B.artikel, SUM(B.menge)
FROM bestellung as B
GROUP BY artikel;

WITH RezeptArtikelTypen (rezept, artikel, menge, einheit) AS (
    SELECT R.rezept, R.artikel, R.menge, R.einheit, A.typ
    FROM rezepte AS R
    LEFT OUTER JOIN artikelliste as A ON R.artikel=A.artikel
),
    RezeptEinkaufsArtikel (Haupt, Unter, Artikel, Menge, Einheit, Typ) AS (
    SELECT
        A.rezept,
        B.rezept,
        B.artikel,
        B.menge,
        B.einheit,
        B.typ
    FROM RezeptArtikelTypen AS A
    FULL JOIN RezeptArtikelTypen as B ON B.rezept=A.artikel
)

Select * from RezeptEinkaufsArtikel
WHERE
    (
        (haupt='Zitronenkuchen' OR unter='Zitronenkuchen')
     OR (haupt='Hefeteig' OR unter='Hefeteig')
    )
    AND typ='Einkaufen' OR typ='Rezept'
ORDER BY haupt;

1740144859456.webp


An dieser Stelle stecke ich nun leider seit Tagen fest.....
- Die Spalten haupt und unter müssten zusammengefasst werden (wenn haupt=null dann Wert aus Spalte unter)
- Die im Result noch vorhandenen Rezepte müssten noch weiter aufgelöst werden.
- Aufsummieren der Mengen anhand der Bestellungen


Mein Ansatz darf natürlich verworfen werden ;-)

Vorab vielen Dank für Eure Hilfe
 
Werbung:
Also das ist jetzt für den Tag und die Uhrzeit schon etwas spät aber ich glaube du begehst ziemlich früh Denkfehler, was dich dann auf einen falschen Weg führt.

Deine Tabelle "rezepte" entspricht nicht der Entität "Rezept". Viel mehr handelt es sich bei dem Inhalt um eine Zwischentabelle (also die Abbildung einer n:m Relation zwischen der eigentlichen Entität "Rezept" und der Entität "Zutat". Sie enthält die Menge einer jeden Zutat, die Maßeinheit und einen Fremdschlüssel Rezept (der auf den Eintrag in der eigentlichen Rezept-Tabelle zeigt) und einen Fremdschlüssel Zutat, der auf einen Eintrag in der Zutatentabelle zeigt.

Die Schlüssel können theoretisch auch die Namen sein, also "Zitronenkuchen" wäre ein Schlüsselkandidat in der Entität Rezept.

Das ganze fällt nicht gleich auf weil du streng genommen, wenn dein Schlüssel Zitronenkuchen ist, kein weiteres Attribut in der Entität Rezept hättest. Und so kommst du dazu diese Verbindungstabelle "RezeptHatZutat" direkt zu "Rezepte" erklärst, was aber irreführend ist. Und nur wenige Zeichen später möchtest du dann Rekursion machen... Nein! Das brauchst du nicht in einem solchen Fall bzw. das ist Blödsinn :-)

Besser zum Verständnis du legst sowohl Rezepte als auch Zutaten als eigene Tabellen an und machst eine Beziehungstabelle, das und ein Select reichen, um zum Einkaufszettel zu kommen. Normalerweise musst du später für die Einkaufsliste nur eine Abfrage machen (keine neue Tabelle anlegen) die alle Rezepte wählt und alle Zutaten aggregiert. Das Aggregat ist etwas schwieriger wegen möglicher Umrechnungen, falls du mit verschiedenen Einheiten wie ml und Liter arbeitetest.
 
Werbung:
ukulele schrieb:
Also das ist jetzt für den Tag und die Uhrzeit schon etwas spät aber ich glaube du begehst ziemlich früh Denkfehler, was dich dann auf einen falschen Weg führt.

Deine Tabelle "rezepte" entspricht nicht der Entität "Rezept". Viel mehr handelt es sich bei dem Inhalt um eine Zwischentabelle (also die Abbildung einer n:m Relation zwischen der eigentlichen Entität "Rezept" und der Entität "Zutat". Sie enthält die Menge einer jeden Zutat, die Maßeinheit und einen Fremdschlüssel Rezept (der auf den Eintrag in der eigentlichen Rezept-Tabelle zeigt) und einen Fremdschlüssel Zutat, der auf einen Eintrag in der Zutatentabelle zeigt.

Die Schlüssel können theoretisch auch die Namen sein, also "Zitronenkuchen" wäre ein Schlüsselkandidat in der Entität Rezept.

Das ganze fällt nicht gleich auf weil du streng genommen, wenn dein Schlüssel Zitronenkuchen ist, kein weiteres Attribut in der Entität Rezept hättest. Und so kommst du dazu diese Verbindungstabelle "RezeptHatZutat" direkt zu "Rezepte" erklärst, was aber irreführend ist. Und nur wenige Zeichen später möchtest du dann Rekursion machen... Nein! Das brauchst du nicht in einem solchen Fall bzw. das ist Blödsinn :-)

Besser zum Verständnis du legst sowohl Rezepte als auch Zutaten als eigene Tabellen an und machst eine Beziehungstabelle, das und ein Select reichen, um zum Einkaufszettel zu kommen. Normalerweise musst du später für die Einkaufsliste nur eine Abfrage machen (keine neue Tabelle anlegen) die alle Rezepte wählt und alle Zutaten aggregiert. Das Aggregat ist etwas schwieriger wegen möglicher Umrechnungen, falls du mit verschiedenen Einheiten wie ml und Liter arbeitetest.
Zum Vergrößern anklicken....
Danke für eine Antwort.

So wie du es beschrieben hast, würde ich es gerne machen. Leider gibt es die Tabellen aber bereits.

Ich muss mit den Tabellen leben wie Sie sind. Auf die Software, welche diese Tabellen erstellt und füllt, habe ich keinen Einfluss und kann daher nichts anpassen. :-(
 
Zurück
Oben