[mySQL]-Abfragen mit Subselect und mehr (schwierig zu beschreiben)

M

me1ssner

Grünschnabel
Guten Abend,

also ich habe folgende Aufgaben, die zur Vorbereitung einer mündlichen Prüfung sind und ich zerbreche mir seit Stunden den Kopf darüber und komme einfach nicht weiter. Dieses Posting wird wohl etwas länger, aber vielleicht hat jemand Lust und Zeit mir zu helfen... ich wäre sehr dankbar.

Da ich bei vielem nicht mal den Hauch einer Ahnung habe, kann ich auch schlecht danach suchen, also entschuldigt bitte, wenn ich hier etwas frage, was schonmal jemand gefragt hat.

Wir haben eine Relation Lieferant mit den Atrributen Firma, Ware und Preis. Das diese Tabelle so nicht unbedingt die schönste Form hat (3NF und so), weiß ich, aber sie ist leider vorgegeben.
Die Waren sind unter anderem Milch, Mehl und noch viele unbekannte andere. Firma ist der Name der Firma und Preis sollte klar sein. Für jede neue Ware wird also eine neue Zeile erstellt mit dem Namen der Firma, der Ware und dem Preis der Ware.

Hier ist einmal die Tabelle für diejenigen, die sie vielleicht in ihre Datenbank zum Testen übernehmen möchten:

Code: 
CREATE TABLE `lieferant` (
  `Firma` varchar(10) NOT NULL default '',
  `Ware` varchar(10) NOT NULL default '',
  `Preis` bigint(10) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Daten für Tabelle `lieferant`
#

INSERT INTO `lieferant` VALUES ('Meier', 'Zucker', 18);
INSERT INTO `lieferant` VALUES ('Meier', 'Eier', 16);
INSERT INTO `lieferant` VALUES ('Meier', 'Mehl', 89);
INSERT INTO `lieferant` VALUES ('Müller', 'Mehl', 345);
INSERT INTO `lieferant` VALUES ('Schmidt', 'Milch', 78);
INSERT INTO `lieferant` VALUES ('Schmidt', 'Mehl', 576);
INSERT INTO `lieferant` VALUES ('Soso', 'Mehl', 78);
INSERT INTO `lieferant` VALUES ('Soso', 'Zucker', 79);
INSERT INTO `lieferant` VALUES ('Majo', 'Majo', 874);
INSERT INTO `lieferant` VALUES ('Majo', 'Eier', 78);
INSERT INTO `lieferant` VALUES ('Bulli', 'Milch', 839);
INSERT INTO `lieferant` VALUES ('Ecki', 'Milch', 3738);
INSERT INTO `lieferant` VALUES ('Soso', 'Eier', 70);

Jetzt haben wir folgende Aufgaben:

1. Wer liefert nur Milch ?

2. Wer liefert alle Waren, die auch die Firma Meier liefert ?

3. Bestimmen sie zu jeder Ware den günstigsten Lieferanten.

Ich habe schon etliche Versuche gemacht, aber bei Frage 1 fehlt mir einfach so eine Art "nur" in mySQL... ich finde da keinen Weg die anderen Tupel auszuschließen, die auch andere Waren liefern. Bei 2. fehlt mir sozusagen ein "Array", in den ich die Waren von Meier packen könnte und mit den Waren der anderen vergleichen könnte. Mit PHP wäre das alles schön einfach, aber wir sollen es ja nur in mySQL lösen...

Wir hatten noch mehr Fragen, aber die haben wir schon gelöst.

An Alle, die sich die Mühe machen, das hier alles zu lesen und mir zu helfen: 1000x Danke

Grüße
Stephan
 
3. Bestimmen sie zu jeder Ware den günstigsten Lieferanten.

SELECT Firma, Ware, MIN(Preis)
FROM lieferant
GROUP BY Ware


Bei den anderen Fragen, habe ich jetzt auf Anhieb keine Lösung (Ich hätte die Tabelle in 3 aufgeteilt ;) ). Vielleicht habe ich heute Abend noch ein bischen Zeit - oder es findet sich bis dahin jemand anderes.


Edit: Ähm ja, wie hpvw gesagt hat: Meine Antwort ist natürlich Mist... :-(
 
Zuletzt bearbeitet:
michel_tr hat gesagt.:
3. Bestimmen sie zu jeder Ware den günstigsten Lieferanten.

SELECT Firma, Ware, MIN(Preis)
FROM lieferant
GROUP BY Ware


Bei den anderen Fragen, habe ich jetzt auf Anhieb keine Lösung (Ich hätte die Tabelle in 3 aufgeteilt ;) ). Vielleicht habe ich heute Abend noch ein bischen Zeit - oder es findet sich bis dahin jemand anderes.
Zum Vergrößern anklicken....
Das funktioniert so nicht.
Eine Lösung wären Subquerys (auch für die anderen Aufgaben), eine andere Lösung zu Aufgabe 3 kannst Du Dir mit Hilfe von Toms Query erarbeiten.

Gruß hpvw
 
Aufgabe 1.:
Mein erster Vorschlag wäre eine Lösung ohne Subselect, allerdings kommen da noch 2 Felder mehr mit raus:
Code: 
SELECT
  `Firma`,
  `Ware`,
  COUNT(DISTINCT `Ware`) AS 'AnzahlProdukte'
FROM
  `lieferant`
GROUP BY
  `Firma`
HAVING
  `AnzahlProdukte` = 1 AND `Ware` = 'Milch'
Wenn man nur den Lieferant haben will, dann braucht man glaube ich doch einen Subselect (erst ab MySQL 4.1 möglich):
Code: 
SELECT
  `lieferant`.`Firma` AS 'NurMilchLieferant'
FROM
  `lieferant`
LEFT JOIN
  (
    SELECT
      `Firma`,
      COUNT(DISTINCT `Ware`) AS 'AnzahlProdukte'
    FROM
      `lieferant`
    GROUP BY
      `Firma`
  ) AS subselect ON `lieferant`.`Firma` = `subselect`.`Firma`
WHERE
  `lieferant`.`Ware` = 'Milch' AND `subselect`.`AnzahlProdukte` = 1
GROUP BY
  `lieferant`.`Firma`
Aufgabe 2.:
Die ist knifflig und dürfte ja mit den Beispieldaten keinen Lieferanten liefern, da es außer Meier keinen gibt, der sowohl Zucker als auch Eier und Mehl liefert. Hier fällt mir auf Anhieb nur eine Subselect-Lösung ein. Mein erster Ansatz war der hier:
Code: 
SELECT
  `Firma`
FROM
  `lieferant`
WHERE
      `Ware` IN (SELECT `Ware` FROM `lieferant` WHERE `Firma` = 'Meier' GROUP BY `Ware`)
  AND `Firma` != 'Meier'
GROUP BY
  `Firma`
Aber das ist ja falsch, denn dann müsste die Frage heißen "Wer liefert sonst noch irgend eine Ware, die auch Meier liefert?". Ist die Frage nur unpräzise formuliert, oder ist es tatsächlich gewollt, dass als Ergebnis "keiner" rauskommt? Ich belasse es mal dabei und kümmere mich um...

Aufgabe 3.:
Das ist wieder einfacher:
Code: 
SELECT
  `Ware`,
  `Firma`,
  `Preis`
FROM
  `lieferant`
WHERE
  `Preis` = (SELECT MIN(`Preis`) FROM `lieferant` AS subselect WHERE `lieferant`.`Ware` = `subselect`.`Ware`)
Das wären so weit meine Lösungsansätze. Ich bin sicher, das kann man alles auch anders lösen - bei SQL gibt's fast immer mehr als einen Weg.
Falls Frage 2 doch so gemeint ist wie sie da steht, dann werd' ich mich noch mal dran versuchen, ich fürchte aber, das wird dann komplizierter..

Grüße,
und viel Glück bei der Prüfung,
Martin
 
Hier noch die Lösung zu Aufgabe 3 ohne Subquery unter Verwendung der sehr gewieften, von hpvw erwähnten Technik gemäß "Tom's Query":
Code: 
SELECT
  `lieferant`.`Ware`,
  `lieferant`.`Firma`,
  `lieferant`.`Preis`
FROM
  `lieferant`
LEFT JOIN
  `lieferant` AS gejoint ON `gejoint`.`Ware` = `lieferant`.`Ware` AND `gejoint`.`Preis` < `lieferant`.`Preis`
WHERE
  `gejoint`.`Firma` IS NULL
 
wow, danke für die vielen Antworten. Ich werde das am Wochenende mal durcharbeiten... auch das mit Toms Query.

Melde mich wieder, wenn ich alles ausprobiert habe... wir haben auch noch andere Lösungen gefunden, die aber wahrscheinlich auch nicht recht funktionieren.

nochmals Danke
 
1. Wer liefert nur Milch ?
SELECT `Firma` FROM `Lieferant` WHERE `Ware`='Milch' AND `Firma` NOT IN(
SELECT `Firma` FROM `Lieferant` WHERE `Ware`!='Milch');

2. Wer liefert alle Waren, die auch die Firma Meier liefert ?
SELECT DISTINCT `Firma` FROM `lieferant` x WHERE NOT EXISTS (
SELECT `Ware` FROM `Lieferant` WHERE `Firma` = 'Meier' AND NOT `Ware` IN (
SELECT `Ware` FROM `Lieferant` WHERE `Firma` = x.`Firma`))
AND NOT `Firma` = 'Meier';

(Diese Lösung ist zunächst etwas verwirrend. Es gibt aber in SQL keine Möglichkeit, Allquantoren auszudrücken, so muss das ganz in eine Form mit Existenzquantoren gebracht werden.)


3. Bestimmen sie zu jeder Ware den günstigsten Lieferanten.
SELECT `Firma`, `Ware`, `Preis` FROM `lieferant` x WHERE `Preis` <= ALL(
SELECT `Preis` FROM `lieferant` WHERE `Ware`=x.`Ware`);
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück