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



## me1ssner (22. September 2005)

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:


```
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


----------



## michel_tr (23. September 2005)

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... :-(


----------



## hpvw (23. September 2005)

michel_tr hat gesagt.:
			
		

> 3. Bestimmen sie zu jeder Ware den günstigsten Lieferanten.
> 
> SELECT Firma, Ware, MIN(Preis)
> FROM  lieferant
> ...


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


----------



## ManicMarble (23. September 2005)

Aufgabe 1.:
Mein erster Vorschlag wäre eine Lösung ohne Subselect, allerdings kommen da noch 2 Felder mehr mit raus:

```
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):

```
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:

```
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:

```
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_


----------



## ManicMarble (23. September 2005)

Hier noch die Lösung zu Aufgabe 3 _ohne_ Subquery unter Verwendung der sehr gewieften, von hpvw erwähnten Technik gemäß "Tom's Query":

```
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
```


----------



## me1ssner (23. September 2005)

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


----------



## Xenokrates (10. Mai 2008)

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`);


----------

