Kopfnuss für MySQL-Profis

[MogliNG]

Hallo Forum,

ich weiß da draußen sind größere MySQL-Könner als ich, daher folgende Profi-Frage:

Tabelle 1 (Auto): id | autoname
---------------
1 | Vectra
2 | Astra
3 | Polo

Tabelle 2 (Ersatzteilpflicht): id | autoID | eindeutigerKey
---------------
1 | 1 | 2
2 | 1 | 1
3 | 2 | 3

Tabelle 3 (Ersatzteile): id | eindeutigerKey | autoID | Beschreibung
---------------
1 | 1 | 1 | Leuchtmittel links, vorne
2 | 2 | 1 | Leuchtmittel rechts, vorne


Es versteht sich von selbst, dass eine Werkstatt möglichst alle Ersatzteile vorrätig hat. In der Realität ist das natürlich nicht immer möglich.

Die Tabellen sind wie folgt verknüpft:
- T2.autoID referenziert auf T1.id
- T2.eindeutigerKey referenziert auf T3.eindeutigerKey

BESONDERHEIT:
Wenn ein Ersatzteil nicht im Lager ist fehlt ein entsprechender Eintrag in Tabelle 3 gänzlich
(es ist also nicht ein der Tabelle enthalten, auch nicht als Dummy mit leerem 'eindeutigerKey' / auch nicht mit NULL).

Verbal-SQL:
Es sollen alle Autos selektiert werden bei denen ALLE Ersatzteile (gemäß Tabelle 2) verfügbar sein müssen und auch tatsächlich sind (gemäß Tabelle 3).
Sobald nur ein einziges Pflichtersatzteil nicht verfügbar ist, darf das Auto nicht aufgelistet werden:
Der 'Astra' darf nicht ausgewählt werden, weil eine Ersatzteilpflicht besteht, jedoch keine Ersatzteile im Lager sind.

Ziel ist es das Ergebnis mit einem einzigen MySQL-Statement zu erhalten.

Das Ergebnis 1, für Super-Profis soll so aussehen:
1 | Vectra
3 | Polo

Alternativ kann das Ergebnis 2 für Profis auch so aussehen, da mit Leichtigkeit alle Fahrzeuge ohne 'Ersatzteilpflicht' separat selektiert werden können:
1 | Vectra

Ich freue mich auf Eure Lösungen - ich selbst habe verzweifelt... :-(

[Plasm]

Ich würd´s vermutlich so lösen:

SELECT *
FROM Auto AS T1
LEFT JOIN Ersatzteilpflicht AS T2 ON T2.autoID=T1.id
LEFT JOIN Ersatzteile AS T3 ON T3.eindeutigerKey=T2.eindeutigerKey

Als Ergebnis erhälst Du
1|Vectra
1|Vectra
2|Astra
3|Polo
Bei beiden Vectra-Zeilen sind alle Spalten ausgefüllt, beim Astra sind alle Spaten aus T3 null und beim Polo sind alle Spalten aus T2 und T3 leer.

Also kannst Du noch folgende Where-Klausel anhängen:
WHERE NOT ISNULL(T3.id) OR ISNULL(T2.id)
Und Du erhälst:
1|Vectra
1|Vectra
3|Polo

Wenn DU jetzt noch gerne 1 Zeile pro Auto haben möchtest kommt noch:
GROUP BY T1.id
hinten dran. Dann sieht das Ergebnis so aus:
1|Vectra
3|Polo

Habe nur eine Lösung erstellt die bei dem angegeben Beispiel funktionert. Ist also nicht 100%ig zuende gedacht - kann u.U. sein, dass ich irgendwas übersehen habe und es bei anderen Daten nicht mehr funktioniert. Hoffe jedoch das es soweit richtig ist :-)

Was ich aber nicht verstehe: Wozu ist eindeutigerKey? Du hast doch der Tabelle Ersatzteilpflicht eine Primäre-ID - wieso nimmst Du die nicht ? Bei der Tabelle Auto hast Du es so gemacht...

Gruß
Plasm

[MogliNG]

Hallo Plasm,

danke für deinen Lösungsversuch. Leider funktioniert dieser nur an dem o.g. Beispieldaten,
das o.g. Verbal-SQL ist jedoch nicht vollständig berücksichtigt.

Es gibt folgende Fälle:
1) Auto; keine Ersatzteilpflich
2) Auto; eine Ersatzteilpflicht, Ersatzteil vorrätig
3) Auto; eine Ersatzteilpflicht, Ersatzteil NICHT vorrätig
4) Auto; mehrfach Ersatzteilpflicht, KEIN Ersatzteil vorrätig
5) Auto; mehrfach Ersatzteilpflicht, ALLE Ersatzteile vorrätig
6) Auto; mehrfach Ersatzteilpflicht, TEILWEISE Ersatzteils vorrätig

Dein SQL-Statement funktioniert bei 1 bis 5, jedoch nicht bei 6.

Tabelle 1 (Auto): id | autoname
---------------
1 | Vectra
2 | Astra
3 | Polo
4 | A4
5 | A6
6 | A8

'Ersatzteilpflicht' sortiere ich mal gemäß o.g. Fälle neu.

Tabelle 2 (Ersatzteilpflicht): id | autoID | eindeutigerKey
---------------
1 | 2 | 1
2 | 3 | 5
3 | 4 | 6
4 | 4 | 7
5 | 5 | 1
6 | 5 | 2
7 | 6 | 1
8 | 6 | 5

Demnach wären folgende Ergebnisse richtig:
1 | Vectra
2 | Astra
3 | Polo
4 | A4
5 | A6

Bitte störe Dich nicht an "eindeutigerKey" dieser ist aus Gründen der Abwärtskompatibilität weiterhin enthalten
und könnte beispielsweise die Artikelnummer des Vorlieferanten oder des Herstellers sein.
(Die Redundanz möchten wir an dieser Stelle nicht normalisieren).

Bis zu Deiner Lösung bin ich auch noch gekommen, hatte diese nicht gepostet, da ich damit
ggf. jemanden "aufs falsche Gleis" gestellt hätte. Vielleicht ist eine andere Herangehensweise besser?
Der letzte Fall ist die wirkliche Kopfnuss - an dieser bin ich schon verzweifelt.
Ein Umweg via PHP mit mehreren Querys wäre sehr unschön.

Daher noch mal die Frage: Wer kann diese Kopfnuss lösen?
Vielleicht Du, Plasm?

[Plasm]

Wenn ich das Problem richtig verstanden habe, hast Du Dich bei der "richtigen Ergebnistabelle" vertan.
Müsste imo dann so aussehen:
1 | Vectra
2 | Astra
3 | Polo
5 | A6

Der A4 gehört dann nicht in die Ergebnismenge, oder ?

[MogliNG]

Hallo Plasm,

richtig. Ein Copy&Paste-Fehler. Der A4 gehört nicht zur Ergebnismenge.

Gewünschte Ergebnismenge:
1 | Vectra
2 | Astra
3 | Polo
5 | A6

[Plasm]

Öhm, sehe gerade... vom Polo ist das Ersatzteil ja auch nicht da - also eher:
1 | Vectra
2 | Astra
5 | A6

Hab ich eben wohl auch übersehen :-(
Bestäige doch bitte noch mal die neue Ergebnismenge

[Plasm]

Angenommen meine letzte gepostete Ergebnismenge ist richtig, würde ich folgendes Vorschlagen:

Es sieht dann ja quasi so aus:
Auto | Vorhanden/Pflichten
-----------------------------
Vectra | 0/0
Astra | 1/1
Polo | 0/1
A4 | 0/2
A6 | 2/2
A8 | 1/2

Als Ergebnis möchtest Du alle Zeilen haben bei denen Vorhandene Ersatzteile = Ersatzteilpflichten zutrifft.

SELECT *, COUNT(T3.id) AS Vorhanden, COUNT(T2.id) AS Pflichten
FROM Auto AS T1
LEFT JOIN Ersatzteilpflicht AS T2 ON T2.autoID=T1.id
LEFT JOIN Ersatzteile AS T3 ON T3.eindeutigerKey=T2.eindeutigerKey
GROUP BY T1.id

Damit bekommst Du eine Ergebnismenge mit allen Autos sowie den Spalten "Vorhanden" und "Pflichten". Nun musst Du nur noch die rauspicken, bei denen die beiden Werte gleich sind. Das geht prima mit der Having-Klausel (hinter dem Group-Statement):
HAVING Vorhanden=Pflichten

Falls Du die Spalten Vorhanden und Pflichten nicht in der Ergebnismenge benötigst, würde die Having-Klausel so aussehen:
HAVING COUNT(T3.id)=COUNT(T2.id)

Gruß Plasm

[MogliNG]

Hallo Plasm,

noch mal richtig.

Gewünschte Ergebnismenge:
1 | Vectra
2 | Astra
5 | A6

also ohne Polo.

[MogliNG]

Hallo Plasm,

Du hast die Lösung gefunden und meinen aufrichtigen Respekt verdient.

Lösung:
SELECT *, COUNT(T3.id) AS Vorhanden, COUNT(T2.id) AS Pflichten
FROM Auto AS T1
LEFT JOIN Ersatzteilpflicht AS T2 ON T2.autoID=T1.id
LEFT JOIN Ersatzteile AS T3 ON T3.eindeutigerKey=T2.eindeutigerKey
GROUP BY T1.id
HAVING COUNT(T3.id)=COUNT(T2.id)

Ich war schon auf einem guten Weg und hatte auch an GROUPE BY mit HAVING und COUNT gedacht. Leider hat mich der MySQL 3.23 bei Statements mit GROUPE BY ohne Fehler total in die Wüste geschickt. Jetzt habe ich gelesen, dass es hier noch kein GROUPE BY gab. Kaum hatte ich den Testserver auf 5.0 umgestellt läuft es. Ich hatte die ganze Zeit den Fehler in meinem SQL-Statement gesucht...

Danke.
Problem gelößt.

[Plasm]

Bei 3.23 gab es das GROUP BY (nicht GROUPE BY)-Statement aber schon und hat da auch schon prima funktioniert...
Naja, wenn´s jetzt geht ist´s ja wurscht.

[MogliNG]

Hallo Plasm,

ich habe Dir eine PN (private Nachricht) geschickt.
Vielen Dank für die Hilfe.

[MogliNG]

Hallo Forum,
Hallo Plasm,

neue Frage zum selben Thema:
Was ist wenn die Ergebnismenge so aussehen soll:

Ergebnis:
T1.id | T1.auto | T2.id | T3.id
1 | Vectra | NULL | NULL
2 | Astra | 1 | 1
5 | A6 | 5 | 1
5 | A6 | 6 | 2
also zwar die Autos, aber nicht reduziert auf je eine Zeile pro Auto.

GROUP BY darf ich dann auf die Temporäre Tabell nicht anwenden (obwohl mir diese ja die Auswahleinschränkung via HAVING ermöglichen würde)

Temorär Ergebnis (vor GROUP BY und HAVING):
id | auto | T2ID | T3ID
1 | Vectra | NULL | NULL
2 | Astra | 1 | 1
3 | Polo | 2 | NULL
4 | A4 | 3 | NULL
4 | A4 | 4 | NULL
5 | A6 | 5 | 1
5 | A6 | 6 | 2
6 | A8 | 7 | 1
6 | A8 | 8 | NULL

Bis hier hin hätte ich es ja noch fast selbst geschafft, aber jetzt bin ich tatsächlich am Ende meines Lateins. Ich weiß gar nicht mehr weiter... Ich brauche noch mal eure Hilfe.

[Plasm]

Hab zwar per PM geschrieben das ich Subselects heute nicht mehr ausprobiere, hier nun aber doch eine mögliche Lösung:

SELECT *
FROM Auto AS T1
LEFT JOIN Ersatzteilpflicht AS T2 ON T2.autoID=T1.id
LEFT JOIN Ersatzteile AS T3 ON T3.eindeutigerKey=T2.eindeutigerKey
WHERE
(
SELECT sum(ISNULL(TS2.id))
FROM Ersatzteilpflicht AS TS1
LEFT JOIN Ersatzteile AS TS2 ON TS2.eindeutigerKey=TS1.eindeutigerKey
WHERE TS1.autoID=T1.id
GROUP BY TS1.autoID
)=0
OR ISNULL(T2.id)

In dem Subselect mache ich nun also eine Anfrage über Ersatzteilpflicht und Ersatzteile wobei ich die Zeilen aufaddiere, in denen die Ersatzteil-Zeilen null sind (also keine Ersatzteile vorhanden). Wenn das Subselect eine 0 zurückliefert, bedeutet das, dass kein Zeile vorhanden ist in der ein Ersatzteil fehlt - also nur die Zeilen wo alle Ersatzteile da sind.

Die Letzte Zeile (Oder-Verknüpfung) muß leider für den Vectra sein. Dort bekommst Du nicht 0 sondern null zurück. Vielleicht kann man das auch schöner abfragen (z.B. indem man das Ergebnis des Subselects in eine Zahl umwandelt - dann müsste aus null ja 0 werden und die Bedingung trifft zu). So eine OR-Verknüpfung ist immer doof, da es die Performance der Abfrage runterzieht. Aber auch die Subselects an sich sind natürlich nicht die schnellsten.

Hab das gerade noch auf die Schnelle "hingesaut" - kann man wie gesagt bestimmt noch schöner lösen, mir ist nur grad nix anderes eingefallen. Vielleicht hat ja noch jmd anderes eine elegantere Methode bzw. Verbesserungsvorschläge?

Gruß
Plasm