Abfrage mit Join(1:n) der in mehreren Spalten etwas bestimmtes enthält

kurm_de

kurm_de

Neuer Benutzer
Beiträge
3
Hallo zusammen,

ich hänge gerade an einer Abfrage.
Und zwar möchte ich aus einer Tabelle mit Rezepten diejenigen selektieren, bei denen die Zutaten über einen JOIN bestimmte Werte in mehreren Spalten enthalten.

Ich habe dazu mal zwei einfache Beispieltabellen konstruiert.
Die Tabelle mit Rezepten sieht so aus:
id | rezeptCode | zutat
=====================
1 | 00001 | mehl
2 | 00001 | milch
3 | 00001 | eier
4 | 00002 | milch
5 | 00002 | eier
6 | 00002 | salz
7 | 00003 | zucker
8 | 00003 | zimt

Und das ist die Tabelle mit den Zutaten:
id | zutat | farbe | zustand
=====================
1 | mehl | weiß | pulver
2 | milch | weiß | flüssig
3 | eier | gelb | flüssig
4 | salz | weiß | körnig
5 | zucker | weiß | körnig
6 | zimt | braun | pulver

Ich möchte jetzt alle Rezepte raus kriegen, wenn Zutaten mit farbe=braun und zustand=körnig enthalten sind.
Somit würde ich in dem Beispiel das Rezepte 00003 erhalten, da hier mit zimt(braun) und zucker(körnig) die Bedingungen erfüllt sind.

Wie muß ich jetzt die Abfrage aufbauen?
In meinen bisherigen Versuchen, in denen ich es mit WHERE und verschachtelten AND, OR versucht habe kam ich nicht zum gewünschten Ergebnis. Entweder ich bekam zu viele Rezepte angezeigt oder gar keine.

Ich hoffe ihr versteht was mein Problem ist und könnt mir helfen...

Viele Grüße
kurm_de
 
Werbung:
Dein Tabellendesign ist nicht gut. Es wäre besser, die Zutat in Rezepte-Tabelle als Foreign Key auf die Zutaten-Tabelle auszuführen. Außerdem, Dein Design skaliert nicht, wenn Du später bei den Zutaten weitere Eigenschaften wie z.B. Geschmack dazunehmen willst.

Auch die Wahl der Datenbank ist unglücklich, meiner Meinung nach. Ich zeige Dir eine Lösung mit PostgreSQL, da geht sowas sehr elegant mit Arrays zu lösen.

Code: 
test=*# select * from zutaten;
 id | zutat  | farbe | zustand
----+--------+-------+---------
  1 | mehl   | weiß  | pulver
  2 | milch  | weiß  | flüssig
  3 | eier   | gelb  | flüssig
  4 | salz   | weiß  | körnig
  5 | zucker | weiß  | körnig
  6 | zimt   | braun | pulver
(6 Zeilen)

test=*# select * from rezepte;
 id | rezept_code | zutat  
----+-------------+--------
  1 |           1 | mehl
  2 |           1 | milch
  3 |           1 | eier
  4 |           2 | milch
  5 |           2 | eier
  6 |           2 | salz
  7 |           3 | zucker
  8 |           3 | zimt
(8 Zeilen)

test=*# select rezept_code, array_agg(zutat) from rezepte group by rezept_code having array_agg(zutat) <@ (select array_agg(zutat) from zutaten where farbe = 'braun' or zustand = 'körnig');
 rezept_code |   array_agg   
-------------+---------------
           3 | {zucker,zimt}
(1 Zeile)

test=*#

Um es kurz zu erklären:

Code: 
test=*# select array_agg(zutat) from zutaten where farbe = 'braun' or zustand = 'körnig';
     array_agg     
--------------------
 {salz,zucker,zimt}
(1 Zeile)

Damit bekomme ich ein Array mit Zutaten, welche entweder braun oder körnig sind. Dazu zählt nun auch Salz.

Code: 
test=*# select rezept_code, array_agg(zutat) from rezepte group by rezept_code;
 rezept_code |     array_agg     
-------------+-------------------
           3 | {zucker,zimt}
           2 | {milch,eier,salz}
           1 | {mehl,milch,eier}
(3 Zeilen)

liefert mir Array mit den Zutaten je Rezept_Code. Nun brauch ich nur noch schauen welches dieser Arrays mit den Zutaten je Rezept in dem zuerst erzeugten Array enthalten ist, das macht der <@ - Operator.

Einfach, oder?
 
okay, anderer Weg:

Code: 
test=*# select rezept_code, count(rezept_code) from rezepte where zutat in (select zutat from zutaten where farbe = 'braun' or zustand = 'körnig') group by rezept_code having count(rezept_code) = 2;
 rezept_code | count
-------------+-------
           3 |     2
(1 Zeile)

Könnte vielleicht sogar MySQL können...
 
Vielen Dank für Deine Lösungen.
Ich würde das so gerne mit dem PostgreSQL ausprobieren, aber hier habe ich nur dieses MySQL zur Verfügung :(

Die zweite Lösung habe ich ausprobiert und hat auch fast funktioniert, aber da gibt es noch ein kleines Problem:
Wenn eine Rezeptur nur 1x mehl und 2x zucker enthält dann ist der count auch = 2 und taucht dann auch im Ergebnis mit auf.
 
kurm_de schrieb:
Vielen Dank für Deine Lösungen.
Ich würde das so gerne mit dem PostgreSQL ausprobieren, aber hier habe ich nur dieses MySQL zur Verfügung :(
Zum Vergrößern anklicken....

PostgreSQL ist OpenSource und frei verfügbar ...

Die zweite Lösung habe ich ausprobiert und hat auch fast funktioniert, aber da gibt es noch ein kleines Problem:
Wenn eine Rezeptur nur 1x mehl und 2x zucker enthält dann ist der count auch = 2 und taucht dann auch im Ergebnis mit auf.
Zum Vergrößern anklicken....

Ah, ja. UNIQUE auf (rezept_code,zutat) kann das aber verhindern.
 
akretschmer schrieb:
Ah, ja. UNIQUE auf (rezept_code,zutat) kann das aber verhindern.
Zum Vergrößern anklicken....
richtig, ich hab das jetzt mit DISTINCT direkt in die Abfrage eingebaut:
Code: 
SELECT t.rezept_code, COUNT(t.rezept_code) 
FROM (SELECT  DISTINCT zutat, rezept_code FROM rezepte) t
WHERE t.zutat IN (SELECT zutat FROM zutaten WHERE farbe = 'braun' OR zustand = 'körnig') 
GROUP BY t.rezept_code 
HAVING COUNT(t.rezept_code) = 2;

Jetzt habe ich noch das Problem, dass wenn ich z.B. nach farbe=weiß und zustand=körnig suche und in einem Rezept nur mehl und milch drinnen sind dieses auch ein count=2 bekommt.
 
Werbung:
Zurück
Oben