MYSQL Query Problem

[InsaneDruid]

Hi

ich habe folgendes problem:

gegeben sind folgende tabellen

table produkt
{
ID
Bezeichnung
}

table rohstoff
{
ID
Bezeichnung
}

table lager
{
ID
Bezeichnung
}

table produkt_rohstoff
{
produkt_ID
rohstoff_ID
}

table lager_rohstoff
{
lager_ID
rohstoff_ID
}

abgefragt werden soll nun, bei gegebener lager.ID=lager_rohstoff.lager_id welche produkte aus den rohstoffen des lagers vollständig hergestellt werden sollen

bislang bekomm ich es nur hin das produkte angezeigt werden, die einen verfügbaren rohstoff enthalten, nicht aber das nur produkte angezeigt werden, die ALLE verfügbaren rohstoffe enthalten. (und nur die verfügbaren)

[CrazyIvan]

Hui,
ich würde mal kurz den Gedankengang darlegen, den man hier für die Lösung braucht:
Wir wollen ja alle Produkte, für die alle Rohstoffe im gegebenen Lager verfügbar sind.
1. Der erste Schritt liegt also darin, für alle Produkte deren jeweilige Rohstoffe zu identifizieren. Das machen wir mit einem INNER JOIN zwischen Produkt und Produkt_Rohstoff.
2. Im zweiten Schritt wollen wir alle Rohstoffe, die im gegebenen Lager verfügbar sind. Dafür brauchen wir einen INNER JOIN zwischen Lager und Lager_Rohstoff.
3. Nun wollen wir wissen, welche Rohstoffe für die entsprechenden Produkte NICHT in diesem Lager verfügbar sind. Denn sobald mindestens ein Rohstoff nicht vorhanden ist, kann das Produkt nicht gebaut werden. Hierzu brauchen wir einen LEFT JOIN der Menge aus 1. mit der Menge aus 2.
Was macht der LEFT JOIN? Er liefert uns für jede Zeile der ersten Menge (mindestens) eine Zeile der zweiten Menge. Ist das Produkt nicht im Lager, so sind alle Spalten der zweiten Menge NULL.
Wir gruppieren nun über "Produkt". Nun können wir über die HAVING-Klausel herausfiltern, welche Produkte nicht gefertigt werden. HAVING ist vergleichbar mit WHERE, nur dass sie auf aggregierte Datensätze Anwendung findet, nicht auf einzelne Zeilen.
Das Einzige, was man dazu nun noch braucht, ist ein Kriterium für diese Klausel. Hierzu nutzen wir eine Aggregatfunktion. Da diese jedoch NULL-Werte ignorieren und wir auf NULL testen wollen, wird mit der ISNULL()-Funktion ein NULL-Wert durch einen beliebigen Anderen ersetzt. Wir nehmen -1, da Deine IDs aller Warscheinlichkeit positiv sind und wir so mit der MIN()-Funktion prüfen können.

Vielleicht wirds am Code etwas ersichtlicher:

SELECT p.Bezeichnung
FROM (Produkt p
INNER JOIN Produkt_Rohstoff pr
ON p.ID = pr.Produkt_ID
)
LEFT JOIN lager_rohstoff lr
ON pr.Rohstoff_ID = lr.Rohstoff_ID
WHERE lr.Lager_ID = [DEINE LAGER_ID]
GROUP BY p.Bezeichnung
HAVING MIN(ISNULL(lr.Rohstoff_ID, -1)) > 0


In Ermangelung einer MySQL-DB kann ichs leider nicht auf syntaktische Korrektheit testen. Falls Fehler auftauchen, schau mal in der Referenz unter "Nested JOIN" nach.

[Marscel]

SELECT p.Bezeichnung
FROM (Produkt p
INNER JOIN Produkt_Rohstoff pr
ON p.ID = pr.Produkt_ID
)
LEFT JOIN lager_rohstoff lr
ON pr.Rohstoff_ID = lr.Rohstoff_ID
WHERE lr.Lager_ID = [DEINE LAGER_ID]
GROUP BY p.Bezeichnung
HAVING MIN(ISNULL(lr.Rohstoff_ID, -1)) > 0

Wenn du MySQL verwendest, muss das IFNULL statt ISNULL sein, aber egal.
SO wird das nicht funktionieren, weil du mit dem LEFT JOIN so, wie er da steht, per sé keine NULL-Rows anjoinen wirst, wenn der Rohstoff in irgendeinem Lager existiert. Ich weiß, man soll Joins nicht über zwei Felder laufen lassen, spontan würde ich aber erstmal "ON ... AND lr.Lager_ID = [ID]" nutzen und die WHERE Bedingung rauswerfen.

[InsaneDruid]

danke euch...

habs jetzt erstmal in dieser form hier hinbekommen, grundidee die mir fehlte war das suchen was NICHT im lager verfügbar ist. eben einfach die umkehrung von where exists(...in()) in unteres where not exists (...not in())

SELECT p2.bezeichnung
FROM produkt p2, rohstoff, produkt_rohstoff
WHERE NOT EXISTS
(
SELECT produkt.id
FROM rohstoff, produkt_rohstoff, produkt
WHERE produkt_rohstoff.produkt_id = produkt.id
AND produkt_rohstoff.rohstoff_id = rohstoff.id
AND rohstoff.id
NOT IN
(
SELECT rohstoff_id
FROM lager_rohstoff
WHERE lager_rohstoff.lager_id = "1"
)
AND c2.id = produkt.id
)
AND produkt_rohstoff.produkt_id = p2.id
AND produkt_rohstoff.rohstoff_id = rohstoff.id

GROUP by p2.id