Select über 3 Tabellen

[cattweasel]

Hallo liebe Community,

Ich habe 5 Tabellen: db_recipe, db_variation, db_ingredient sowie db_variation2recipe und db_ingredient2variation.

Die Tabelle db_ingredient besitzt folgende Keys: id, title.
Darin befinden sich reine Zutaten, z.B. Salz oder Zuckerrüben.

Die Tabelle db_variation besitzt folgende Keys: id, name, measure, amount.
Darin befinden sich Variationen von Zutatenmöglichkeiten, z.B. 1 Prise Salz oder 25g Salz oder 1 Packung Salz.

Die Tabelle db_recipe besitzt alle möglichen Informationen von verschiedenen Rezepten.
Über die Tabellen db_variation2recipe und db_ingredient2variation werden jeweils Rezept und Variation,
sowie Zutat und Variation per id verknüpft. Beide Tabellen haben haben 3 Keys: id, id_(variation|ingredient), id_(recipe|variation).

Nun möchte ich gerne verschiedene Zutaten (keine Variationen) angeben und alle Rezepte, auf die die gegebenen Zutaten
zutreffen, erhalten. Für eine einzige Zutat habe ich folgende, funktionierende Abfrage gebaut:

SELECT t1.id, t1.title FROM db_recipe t1, db_variation2recipe t2, db_ingredient2variation t3 WHERE t3.id_ingredient=X AND t3.id_variation=t2.id_variation AND t2.id_recipe=t1.id; ( X => Die ID einer beliebigen Zutat )

Hat jemand eine Idee, wie ich die selbe Logik auf mehrere Zutaten erweitern kann?
Jedes Rezept muss alle gegebenen Zutaten in beliebiger Variation besitzen.


Vielen Dank und Liebe Grüße

cattweasel

[defo]

t3.id_ingredient=X

ersetzen durch

t3.id_ingredient in (x,y,z)

Die Frage ist dabei, in welchem Kontext das Verfahren eingesetzt werden soll.
Report, Grid, Maske/Liste, ...

Wenn das Select Statement dynamisch im Client konstruiert werden kann, kommst Du damit recht weit.
Anonsten sollten die ausgewählten Zutaten evtl. in einer tempörären, user spezifischen Tabelle eingetragen werden und die dann mit in das -dann statische- SQL Statement reingejoined werden.
Alternativ wäre noch anstelle der Temp Table die Möglichkeit einer SP, die eine Liste der gewählten Zutaten ausspukt.
Ist natürlich auch alles eine Frage des verwendeten RDBMS.

[Jürgen Thomas]

defos Vorschlag kann bei Bedarf erweitert werden:

t3.id_ingredient in (SELECT irgendwas)

Das ist möglich, sofern das Abfrageergebnis eine Liste mehrerer Werte liefert. Jürgen

[cattweasel]

Hallo,

vielen Dank erstmal für die Antworten. Zuerst einmal: Das Verfahren wird für ein Grid verwendet und das Statement kann bereits beim Client gebaut werden. Die Idee von defo hatte ich auch schon einmal probiert, allerdings ist das nicht das Ergebnis, welches ich haben möchte.

Wenn ich z.B. nach der Zutaten-ID 23 (Salz) suche, erhalte ich 719 Rezepte. Wenn ich nun nach den Zutaten 23 und 50 (Salz und Pfeffer) suche, bekomme ich mehr als 900 Rezepte. Mir ist bewusst, dass ich nun alle Rezepte erhalte, welche Salz ODER Pfeffer benötigen. Ich möchte aber alle Rezepte, welche Salz UND Pfeffer benötigen.

Liebe Grüße,
cattweasel

[Falk Prüfer]

Hallo,

...möchte aber alle Rezepte, welche Salz UND Pfeffer benötigen.

dann Ergänze das Statement um
[highlight=sql]
...
group by t1.id, t1.title
having count(distinct t3.id_ingredient) = 2
[/highlight]

Gruß Falk

[cattweasel]

Super, vielen Dank an defo und Falk Prüfer!
Das resultierende Statement gibt mir genau das Ergebnis, was ich erwartet habe.

[cattweasel]

Hallo,

ich habe die Datenbank nun mit ein paar Datensätzen befühlt (ca. 20.000 Rezepte) und muss feststellen, dass das Statement noch nicht so das beste ist. Folgende Abfrage braucht bei etwa 20.000 Rezepten knapp 10 Sekunden:

SELECT DISTINCT t1.id, t1.title FROM db_recipe t1, db_variation2recipe t2, db_ingredient2variation t3 WHERE t3.id_ingredient IN (1, 2, 3) AND t3.id_variation=t2.id_variation AND t2.id_recipe=t1.id GROUP BY t1.id HAVING COUNT(DISTINCT t3.id_ingredient) = 3;

Das ist natürlich viel zu lange. Hat jemand einen Tipp, wie ich das selbe Ergebnis in kürzerer Zeit bekomme?

Danke und Liebe Grüße
cattweasel

[Jürgen Thomas]

1. IN ist langsamer als BETWEEN...AND oder >= mit <=.
2. HAVING kostet viel Zeit. Aber da du COUNT prüfen willst, geht es vermutlich nicht, die WHERE-Bedingung zu verbessern. Zur Erläuterung siehe SQL: Einschränkung mit HAVING.
3. Prüfe einmal, ob auf t3.id_ingredient, t3.id_variation, t2.id_variation, t2.id_recipe einzelne Indizes gesetzt sind (nicht nur kombinierte). Da verhalten sich die DBMS unterschiedlich, ob bei einem kombinierten Index die Einzelindizes automatisch erstellt werden.

[cattweasel]

Hallo Jürgen,

zu erst einmal Danke für deine Antwort.

1. Ich habe IN mit <= und >= ersetzt und konnte leider keine deutliche Verbesserung feststellen.
2. Danke für die Lektüre. Allerdings beweist diese deine Aussage. Das muss wohl so im Statement stehen bleiben :-/
3. Verstehe ich nicht. Kannst du mir das bitte etwas genauer erläutern? (Ich habe leider kein großes Wissen über SQL)

Danke und Grüße
cattweasel

Edit: zu Punkt 3: Die beiden Tabellen besitzen eigene Indizes!

[defo]

Edit: zu Punkt 3: Die beiden Tabellen besitzen eigene Indizes!

Das war vermutlich nicht gemeint.
Ist ein Index überhaupt vorhanden? Offenbar ja.
Nun ist die Frage, welche Felder indiziert sind.
- Jeweils nur die Primärschlüssel der 3 Tabellen?
- Auch (alle) Fremdschlüssel (der Felder, die DU in Deinem Statement verwendest)?
- Oder sind auch Feldkombinationen indiziert.

Je nach DB System werden einzeln indizierte Felder oder kombinierte Indizies zu verschieden schnellen Ergebnissen führen.

Was evtl auch etwas hilft:
Den Group / HAving Part nur auf db_ingredient2variation anzuwenden und nicht den "Rest" mit durzukauen. Im 2.Schritt nur die passenden db_recipe, db_variation2recipe anhängen (verschachteltes SQL).

Ganz anderer Ansatz:
Wenn das Statement sowieso dynamisch zusammengebaut wird (oder werden kann), kannst Du je Zutat ein separates Select auf db_ingredient2variation machen und fest über variation joinen. Das spart Group, Count Having komplett.

Ich vermute allerdings, dass keine gute Indizierung vorliegt, da 10 Sekunden für 20T Records nicht so toll ist.