Abfrage über zwei Tabellen

[smoon]

Hallo.

mein Problem ist ich möchte einen Datensatz nur ausgeben, wenn ein Eintrag in anderer Tabelle vorhanden ist. Bspw. sind in einer Tabelle alle Kundendaten und in einer anderen deren Bestellungen. Jetzt möchte ich nur die Kunden ausgeben, welche schon Bestellungen aufgegeben haben (also einen Eintrag in der Tabelle "Bestellung" haben). Bisher hab ich das immer mit zwei SQL-Abfragen gemacht zu erst alle Kunden "geholt" und dann abgefragt ob Bestellungen vorhanden sind. Jetzt würde ich gern wissen ob man dies nicht mit einer einzelnen Abfrage erreichen kann. Meine Erfolge halten sich dabei leider in Grenzen. Entweder wurden gar keine Kunden ausgegeben und des wurden die selben mehrfach ausgegeben (einmal pro eingetragener Bestellung).


Gruß Smoon

[Nobbie]

SELECT kunden.name
FROM kunden, bestellungen
WHERE kunden.id = bestellungen.kunden_id
GROUP BY kunden.name

Ich habe jetzt irgendwelche Tabellennamen und Spaltennamen erfunden, aber die sollten bei Dir recht ähnlich sein.

[smoon]

Besten dank

[Nobbie]

Genau genommen ist das allerdings unsauber, wenn zwei Kunden den gleichen Namen haben, geht das schief. Konsequent sollte man nach der id gruppieren:

SELECT kunden.id
FROM kunden, bestellungen
WHERE kunden.id = bestellungen.kunden_id
GROUP BY kunden.id

Zur Ausgabe der Kundendaten machst Du dann jeweils einen eindeutigen Select auf die jeweilige Kunden.id.

[Xardas der Dunkle]

Cross-Joins sind aber auch unsauber ... bzw. sollten nur verwendet werden wenn man wirklich ALLE Daten mit einander in Beziehung setzen will/muss.

Es empfiehlt sich meist eher ein INNER oder LEFT/RIGHT-Join:

Code: Select all

SELECT kunden.id
FROM kunden INNER JOIN bestellungen ON (kunden.id = bestellungen.kunden_id)
GROUP BY kunden.id

[Nobbie]

Cross-Joins sind aber auch unsauber

Nicht bei MySQL, da MySQL Cross Joins und Inner Joins gleich behandelt:

Aus http://dev.mysql.com/doc/refman/5.1/de/join.html

In MySQL ist CROSS JOIN syntaktisch ein Äquivalent zu INNER JOIN (diese lassen sich gegeneinander

Und MySQL 3.0 konnte nicht einmal einen Inner Join mit ON-Klausel (daher haben sich die Cross Joins dort etabliert).

Ein LEFT/RIGHT Join wäre hier zudem der falsche Lösungsansatz, weil er auch die Kunden liefert, die keine Bestellungen ausgeführt haben.

[Xardas der Dunkle]

Ein LEFT/RIGHT Join wäre hier zudem der falsche Lösungsansatz, weil er auch die Kunden liefert, die keine Bestellungen ausgeführt haben.

Habe ich ja auch nicht benutzt. Weil er wie du schon sagst nicht angebracht ist. Ich meinte nur das sie schneller sind als ein Cross-Join.

Und da steht nur Syntaktisch. Zudem:

INNER JOIN wird bei einer ON-Klausel und CROSS JOIN andernfalls verwendet.

Ich denke man sollte trotzdem lieber einen INNER-JOIN dem CROSS-JOIN vorziehen.
Vor allem kann man so (wenn man auf andere Dinge wie z.B. LIMIT verzichtet) die Engine relativ einfach austauschen.

[Nobbie]

Ich denke man sollte trotzdem lieber einen INNER-JOIN dem CROSS-JOIN vorziehen.

Ne, sehe ich keinen Grund. Der SQL Interpreter generiert aus CROSS mit WHERE exakt dasselbe wie aus INNER mit ON. Vor allem habe ich ja selbst schon mit MySQL 3.x programmiert, als der INNER JOIN mit ON gar nicht unterstützt wurde - das ist genau genommen von MySQL so gewollt, dass man CROSS Joins mit WHERE benutzt. Ich finde diese Unterscheidung ohnehin philosophischer Natur, denn die Fundstelle ist die gleiche, und dann gehört es zu einem guten SQL Parser, das Coding zu optimieren.

Vor allem kann man so (wenn man auf andere Dinge wie z.B. LIMIT verzichtet) die Engine relativ einfach austauschen.

Da muss man sich aber heftig einschränken, denn eine Umstellung von MySQL auf Oracle (nur als Beispiel) ist eine ganz schöne Arbeit. Eigentlich sollte SQL ja standardisiert sein, aber wer MySQL benutzt, der hat die Entscheidung schon gefällt, dass er dabei bleiben will und dann braucht man auch nicht kompatibel zu arbeiten. Es wird sowieso nicht kompatibel.

Das LIMIT ist beispielsweise nicht ersetzbar (MySQL kennt keine andere Syntax), andererseits unverzichtbar. Und ganz schlimm sind die von MySQL geforderten Backticks, damit Eigennamen nicht mit reservierten Wörtern kollidieren. Andererseits wäre es auch mal für viele "Laienprogrammierer" ganz heilsam, wenn sie mit einem ANSI-SQL arbeiten würden, denn gerade im Umgang mit Gruppierungen wird ziemlicher Mist programmiert (es werden Felder selektiert, nach denen gar nicht gruppiert wurde - bei MySQL kommt dann ein zufälliges Ergebnis zur Laufzeit heraus, bei Oracle gibt es einen fetten SQL Error, letzteres halte ich für richtig).

Also insgesamt finde ich das nicht sonderlich wichtig, ich weiß, dass ich mich mit MySQL auf einer Insel befinde, aber für die allermeisten WWW-Anwendungen ist das vollkommen ausreichend.

[Xardas der Dunkle]

Also insgesamt finde ich das nicht sonderlich wichtig, ich weiß, dass ich mich mit MySQL auf einer Insel befinde, aber für die allermeisten WWW-Anwendungen ist das vollkommen ausreichend.

Magst du recht haben, ich selber schreibe eigentlich auch nur für MySQL. Weil das neben dem Halbwissen in Access welches ich aus der Schule kenne eigentlich die einzigste Datenbank ist mit der ich mich wirklich aus einander gesetzt habe.

Das LIMIT ist beispielsweise nicht ersetzbar (MySQL kennt keine andere Syntax), andererseits unverzichtbar.

Habe mich etwas unglücklich ausgedrückt. Meinte es eigentlich so wie die meisten Frameworks es machen, nämlich das die Eingrenzung nachträglich über eine Funktion an den Query angehängt wird. Entsprechend wie die Syntax des verwendeten Drivers es fordert.

Und ganz schlimm sind die von MySQL geforderten Backticks, damit Eigennamen nicht mit reservierten Wörtern kollidieren.

Findest du die von Access geforderten [] etwa besser? Ich finde die machen das ganze nur unleserlich.

(es werden Felder selektiert, nach denen gar nicht gruppiert wurde - bei MySQL kommt dann ein zufälliges Ergebnis zur Laufzeit heraus

Hmh. Also wenn du den Mist meinst den Access auch fabriziert:

Code: Select all

SELECT [id], [vorname], [nachname], SUM([kinder]) AS [anzahl_kinder]
FROM [tabelle] GROUP BY [nachname], [vorname], [id]

Access zwingt einen alle Felder ins GROUP-BY zu schreiben die im SELECT stehen. Wo drin liegt der Sinn? Wenn ich nach dem Nachnamen gruppieren will möchte ich nicht die ID noch im Group-By haben die das ganze Group-By damit untauglich macht, da diese ja eindeutig ist ...

[Nobbie]

Findest du die von Access geforderten [] etwa besser? Ich finde die machen das ganze nur unleserlich.

Mindestens genauso grausam. Da sind die Programmierer nur zu faul, eine kontextorientierten Parser zu schreiben, der von alleine erkennt, ob an dieser Stelle ein Eigenname steht.

Also wenn du den Mist meinst den Access auch fabriziert:

Code: Select all

SELECT [id], [vorname], [nachname], SUM([kinder]) AS [anzahl_kinder]
FROM [tabelle] GROUP BY [nachname], [vorname], [id]

Access zwingt einen alle Felder ins GROUP-BY zu schreiben die im SELECT stehen. Wo drin liegt der Sinn?

Der Sinn ist in diesem Fall richtig, so ist es im ANSI SQL festgelegt (und das zu Recht). Man kann nur Felder selektieren, die im GROUP BY erfasst wurden, oder man muss Aggregatfunktionen (beispielsweise SUM(), oder MIN() oder MAX() usw.) benutzen. Welchen Sinn das hat - ganz einfach: sonst sind die Ergebnisse nicht vorhersehbar. Was soll denn hier herauskommen:

SELECT id, vorname .... GROUP BY vorname

Welchen Wert soll "id" denn haben nachher (nehmen wir an, wir hätten vier mal "Xardas" in der Tabelle mit den id's 5,6, 17 und 25. Welchen Wert für id ergibt dieser SELECT für den Vornamen "Xardas"? Das ist fehlerhaftes SQL.

[Xardas der Dunkle]

Welchen Wert soll "id" denn haben nachher (nehmen wir an, wir hätten vier mal "Xardas" in der Tabelle mit den id's 5,6, 17 und 25. Welchen Wert für id ergibt dieser SELECT für den Vornamen "Xardas"? Das ist fehlerhaftes SQL.

Es ist trotzdem Schwachsinn, was bringt eine Gruppierung die man nicht nutzen kann?
Man sollte eher dafür sorgen das die Gruppierung sortierbar wird.

Mindestens genauso grausam. Da sind die Programmierer nur zu faul, eine kontextorientierten Parser zu schreiben, der von alleine erkennt, ob an dieser Stelle ein Eigenname steht.

Bei Access haben die Dinger eh mehr damit zu tun das dieses Programm von Leuten benutzt wird die von Programmierung überhaupt keine Ahnung haben und Sonderzeichen in Feldnamen benutzen.
Da hilft auch kein Kontextabhängiger Parser ...

Code: Select all

SELECT [Vor & Nachname], [Alter] FROM [Kunden]

[Nobbie]

Es ist trotzdem Schwachsinn, was bringt eine Gruppierung die man nicht nutzen kann?

Wieso kann man die nicht nutzen? Verstehe ich nicht.

Man sollte eher dafür sorgen das die Gruppierung sortierbar wird.

Die Fundstelle ist doch sortierbar!? Man kann doch im oberen Beispiel noch ein ORDER BY Vorname anfügen. Nur Sortierungen auf Felder, die nicht zur Gruppierung gehören, sind ja noch sinnloser, weil deren Inhalt ja nicht bestimmbar ist. Eine ORDER BY Anweisung ist ein "Ausgabefilter" (im Gegensatz zu einer WHERE-Klausel, die ein "Eingabefilter" ist), d.h. während die WHERE-Klausel auf die vollständige Eingabemenge angewendet wird, wird die ORDER BY Anweisung erst nach Ermittelung der Fundstelle auf diese angewandt.

[Xardas der Dunkle]

Die Fundstelle ist doch sortierbar!? Man kann doch im oberen Beispiel noch ein ORDER BY Vorname anfügen. Nur Sortierungen auf Felder, die nicht zur Gruppierung gehören, sind ja noch sinnloser, weil deren Inhalt ja nicht bestimmbar ist. Eine ORDER BY Anweisung ist ein "Ausgabefilter" (im Gegensatz zu einer WHERE-Klausel, die ein "Eingabefilter" ist), d.h. während die WHERE-Klausel auf die vollständige Eingabemenge angewendet wird, wird die ORDER BY Anweisung erst nach Ermittelung der Fundstelle auf diese angewandt.

Ich meinte ja auch eine Vorsortierung.
Die Gruppierung läuft ja im Grunde wie folgt ab:

Code: Select all

$data = array(
     array('id' => 2, 'surname' => 'Meier', 'firstname' => 'Peter'),
     array('id' => 3, 'surname' => 'Andreas', 'firstname' => 'Hans'),
     array('id' => 4, 'surname' => 'Schmidth', 'firstname' => 'Susanne'),
     array('id' => 6, 'surname' => 'Meier', 'firstname' => 'Alexander'),
     array('id' => 7, 'surname' => 'Peters', 'firstname' => 'Max'),
);
$newData = array();
$groups = array();
$groupBy = 'surname';

foreach($data as $row) {
     if(!isset($groups[ $row[ $groupBy ] ])) {
           $newData[] = $row;
           $groups[ $row[ $groupBy ] ] = true;
     }
} 

In $newData sind bis auf "Meier Alexander" alle Datensätze enthalten, weil dieser weg-gruppiert wurde.
Und wieso? Weil dieser erst nach "Meier Peter" ausgelesen wird. Und das wird er weil das Array nach der id sortiert ist.
Würde man nun vor der Gruppierung eine Vorsortierung vornehmen können könnte man das Ergebnis entsprechend verändern.

Wieso kann man die nicht nutzen? Verstehe ich nicht.

Ich meine in Verbindung mit Feldern mit eindeutigem Wert wie z.B. einer ID.
Was bringt die Gruppierung wenn du nachträglich doch selber über eine eigene Gruppierungs-Schleife filtern muss weil der Driver einen dazu zwingt die ID mit ins GROUP-BY zu schreiben!

[Nobbie]

Das klingt jetzt hart, aber Du hast einfach das SQL Konzept in Verbindung mit Gruppierung nicht verstanden. Was Du erreichen willst, kann man selbstverständlich in SQL programmieren, aber nicht mit einem simplen GROUP, weil das so nicht funktioniert.

Wie ich jetzt erst erkenne, hast Du auch nicht die syntaktische Bedeutung verstanden, was es heißt, im GROUB BY mehrere Spalten anzugeben. Um Dir den Sinn davon näherzulegen, stelle ich einmal eine einfache Aufgabe, die prädestiniert ist, das Konzept und die Arbeitsweise des GROUP BY zu erläutern:

Es geht um das (sicherlich jedem bekannte) "Problem", dass man wissen will, welcher Name im Telefonbuch am häufigsten vertreten ist (Vorname, Nachname). Dazu stelle Dir eine Tabelle vor, die u.a. den Vornamen und den Nachnamen der Teilnehmer als Spalten besitzt. Die anderen Spalten (Telefonnummer, id usw.) brauchen wir jetzt nicht.

Wie würdest Du diese Problematik in SQL (MySQL) lösen? Nochmal die Frage: welcher Name (Vorname plus Nachname) taucht am häufigsten im Telefonbuch auf, und wie häufig taucht er auf? In Düsseldorf ist es übrigens angeblich Peter Müller (od. Peter Meier - weiß ich nicht mehr).

[Xardas der Dunkle]

Ich hätte es so gemacht:

Code: Select all

SELECT `firstname`, `surname`, COUNT(*) AS `count_of_names`
FROM `phone_book`
GROUP BY `surname`, `firstname`
ORDER BY `count_of_names` DESC
LIMIT 1

Code: Select all

--
-- Tabellenstruktur für Tabelle `phone_book`
--

CREATE TABLE IF NOT EXISTS `phone_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `surname` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `phone` varchar(255) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=9 ;

--
-- Daten für Tabelle `phone_book`
--

INSERT INTO `phone_book` (`id`, `firstname`, `surname`, `phone`) VALUES
(1, 'Peter', 'Müller', '12345'),
(2, 'Franz', 'Schulze', '789'),
(3, 'Susanne', 'Müller', '564565'),
(4, 'Peter', 'Müller', '5859'),
(5, 'Frank', 'Ferdinand', '7566346'),
(6, 'Helene', 'Schmidth', '989876'),
(7, 'Franziska', 'Müller', '4234325'),
(8, 'Hans', 'Peters', '332434');