komplexe mySQL Abfrage

[Thomas Mang]

Hallo Spezialisten ;)

Mich plagt derweil eine grosse schwierigkeit

ich vereinfache es mal auf folgende tabellen:

fall
  id
  name

fall_historie
  id
  fall_id
  fall_beschreibung
  zeitpunkt

Sinn und zweck ist einfach eine Historie zu haben, wo Änderungen an einer Fallbeschreibung zu einem gewissen Zeitpunkt gespeichert werden

In fall_id steht klarerweise dieselbe id, wie in id der tabelle fall ;)

Nun gebe ich über ein PHP Skript einen zeitpunkt an (nennen wird mal $zeitpunkt ;o)  ) und ich hätte gerne eine mySQL Abfrage, die mir den Namen des Falles anzeigt, und die Beschreibung wie sie zuletzt war VOR $zeitpunkt ... Und das dann zudem noch für ALLE Fälle in Fall mit einer DB Abfrage. Geht sowas??

zwar krieg ich _einen_ passenden datensatz mit:

SELECT f.name, h.beschreibung
FROM fall AS f, fall_historie AS h
WHERE f.id = h.fall_id AND $zeitpunkt > h.zeitpunkt
ORDER BY zeitpunkt
LIMIT 0,1

2 Sachen wo ich nicht wirklich durchblicke sind LEFT JOIN und der Unterschied zwischen HAVING und WHERE ... Falls mir eins von den 2 hier helfen sollte, wär ich froh um ne kleine Erklärung, warum sie das tun, was sie tun ;)

[Axel Richter]

Hallo,

fall
  id
  name

fall_historie
  id
  fall_id
  fall_beschreibung
  zeitpunkt

Nun gebe ich über ein PHP Skript einen zeitpunkt an (nennen wird mal $zeitpunkt ;o)  ) und ich hätte gerne eine mySQL Abfrage, die mir den Namen des Falles anzeigt, und die Beschreibung wie sie zuletzt war VOR $zeitpunkt ... Und das dann zudem noch für ALLE Fälle in Fall mit einer DB Abfrage. Geht sowas??

zwar krieg ich _einen_ passenden datensatz mit:

SELECT f.name, h.fall_beschreibung
FROM fall AS f, fall_historie AS h
WHERE f.id = h.fall_id AND $zeitpunkt > h.zeitpunkt
ORDER BY zeitpunkt
LIMIT 0,1`

Das es nur ein Datensatz ist liegt doch nur am LIMIT.
SELECT f.name, h.fall_beschreibung
FROM fall AS f, fall_historie AS h
WHERE f.id = h.fall_id AND $zeitpunkt > h.zeitpunkt
ORDER BY zeitpunkt
Bringt ein Resultset mit _allen_ namen aus fall mit der fall_beschreibung aus fall_historie, für die es einen Eintrag in fall_historie gibt, dessen zeitpunkt < $zeitpunkt ist, sortiert nach zeitpunkt. Dadurch, durch die Sortierung, können die Fälle natürlich durcheinander geraten, also nicht aufeinander folgend stehen. Eventuell solltest Du hauptsächlich nach h.fall_id und nur innerhalb gleicher ids nach zeitpunkt sortieren.
...
ORDER BY h.fall_id, zeitpunkt

2 Sachen wo ich nicht wirklich durchblicke sind LEFT JOIN und der Unterschied zwischen HAVING und WHERE ... Falls mir eins von den 2 hier helfen sollte, wär ich froh um ne kleine Erklärung, warum sie das tun, was sie tun ;)

Das, was Du da machst, ist ein INNER JOIN. Man könnte diesen auch so formulieren:
SELECT f.name, h.fall_beschreibung
FROM fall AS f INNER JOIN fall_historie AS h ON f.id = h.fall_id WHERE $zeitpunkt > h.zeitpunkt
ORDER BY zeitpunkt

Ein LEFT JOIN:
SELECT f.name, h.fall_beschreibung
FROM fall AS f LEFT JOIN fall_historie AS h ON f.id = h.fall_id

würde alle Datensätze der links (LEFT) stehenden Tabelle, also fall, einbeziehen, auch, wenn für diese kein Eintrag mit fall_id in der Tabelle fall_historie steht. Das Feld fall_beschreibung hätte dann den Wert NULL.

Wenn das gewünscht wäre, könnte natürlich nicht einfach
SELECT f.name, h.fall_beschreibung
FROM fall AS f LEFT JOIN fall_historie AS h ON f.id = h.fall_id
WHERE $zeitpunkt > h.zeitpunkt
geschrieben werden, weil natürlich, bei den zum INNER JOIN zusätzlichen Datensätzen, auch h.zeitpunkt den Wert NULL hätte. Da dieser die Bedingung $zeitpunkt > h.zeitpunkt nicht erfüllt, würde das WHERE diese Datensätze geich wieder eliminieren. Man müsste also
SELECT f.name, h.fall_beschreibung
FROM fall AS f LEFT JOIN fall_historie AS h ON f.id = h.fall_id
WHERE $zeitpunkt > h.zeitpunkt OR h.zeitpunkt IS NULL
schreiben. Die Syntax der Prüfung auf NULL ist abhängig vom SQL Dialekt.

HAVING wird, statt WHERE, verwendet, um Bedingungen für gruppierte Recordsets zu formulieren. Du brauchst es also nur, wenn GROUP BY verwendet wird.

viele Grüße

Axel

[Thomas Mang]

Das es nur ein Datensatz ist liegt doch nur am LIMIT.
SELECT f.name, h.fall_beschreibung
FROM fall AS f, fall_historie AS h
WHERE f.id = h.fall_id AND $zeitpunkt > h.zeitpunkt
ORDER BY zeitpunkt
Bringt ein Resultset mit _allen_ namen aus fall mit der fall_beschreibung aus fall_historie, für die es einen Eintrag in fall_historie gibt, dessen zeitpunkt < $zeitpunkt ist, sortiert nach zeitpunkt. Dadurch, durch die Sortierung, können die Fälle natürlich durcheinander geraten, also nicht aufeinander folgend stehen. Eventuell solltest Du hauptsächlich nach h.fall_id und nur innerhalb gleicher ids nach zeitpunkt sortieren.

erm. ja ... ich möchte in der Ausgabe ja _alle_ daten aus fall haben, aber die von fall_historie limitiert auf eins... das ist der kleine knackpunkt der sache...

So dass ich im Endeffekt alle meine Fälle hab, und in einer Tabelle der ich ein Datum übergebe sehen kann, welchen Status alle diese Sachen jeweils diesem Zeitpunkt hatte ... alle anderen zeitpunkte sollen nicht mit ausgegeben werden, sondern alle fälle und jeweils nur ein datensatz zu jedem Fall, aus fall_historie.

als konkretes Beispiel:
nochmal die Tabellen:

fall
  id
  name

fall_historie
  id
  fall_id
  fall_beschreibung
  zeitpunkt

sagen wir mal in fall steht drin:
1 - Projekt Rot
2 - Projekt Blau
3 - Projekt Grün

in fall_historie steht drin:
1 - 1 - Eröffnung - 1.1.2004
2 - 1 - erster Entwurf - 3.1.2004
3 - 2 - Eröffnung - 7.1.2004
4 - 2 - erster Entwurf - 8.1.2004
5 - 3 - Eröffnung - 9.1.2004
6 - 2 - zweiter Entwurf - 10.1.2004
7 - 1 - zweiter Entwurf - 12.1.2004
8 - 2 - Abschluss - 15.1.2004
9 - 1 - Abschluss - 16.1.2004

Bei zB Eingabe des Zeitpunktes: 4.1.2004
Ausgabe:
Projekt Rot - erster Entwurf

oder zB als Zeitpunkt dann : 10.1.2004
erwünschte Ausgabe:
Projekt Rot - erster Entwurf
Projekt Blau - zweiter Entwurf
Projekt Grün - Eröffnung

oder nachher Zeitpunkt: 17.1.2004
erwünschte Ausgabe:
Projekt Rot - Abschluss
Projekt Blau - Abschluss
Projekt Grün - Eröffnung

und da hab ich keine genaue Idee, wie ich das verknüpfen kann :(

Versteh ich das übrigens mit den Joins richtig, das sich left, right und inner join nur unterscheiden, wenn tabellenfelder NULL sind und nicht vorhanden?

Vielen Dank bis hierher ;)

[Axel Richter]

Hallo,

erm. ja ... ich möchte in der Ausgabe ja _alle_ daten aus fall haben, aber die von fall_historie limitiert auf eins... das ist der kleine knackpunkt der sache...

Nein, das ist der große Knackpunkt. Jedenfalls sehe ich keine Lösung mit _einer_ Abfrage. Da MySQL nicht zu meinen Hobbys gehört, weiß ich nicht, ob MySQL SELECTs joinen kann. Wenn ja, musst Du die Tabelle fall mit einem auf jeweils einen Datensatz je fall_id limitierten SELECT der Tabelle fall_historie, welcher den zeitpunkt einschränkt, joinen. Wenn nein, dann wird eine Programmiersprache das aus zwei Resultsets zusammenbringen müssen.

Versteh ich das übrigens mit den Joins richtig, das sich left, right und inner join nur unterscheiden, wenn tabellenfelder NULL sind und nicht vorhanden?

Naja, im Prinzip ist das so. Genau liefert:

ein INNER JOIN eine Datensatzmenge mit Feldinhalten aus zwei Tabellen, bei denen die Datensätze der ersten Tabelle Entsprechungen, über die JOIN-Bedingung, in der zweiten Tabelle haben,

ein LEFT JOIN eine Datensatzmenge mit Feldinhalten aus zwei Tabellen, wobei alle Datensätze der LEFT-Tabelle im Resultset sind, egal, ob diese Entsprechungen in der zweiten Tabelle haben, wenn ja, werden diese Entsprechungen als Feldinhalte der Felder der zweiten Tabelle dargestellt, wenn nein, werden die nicht vorhandenen Feldinhalten der Felder der zweiten Tabelle als NULL-Werte dargestellt.

Ein RIGHT JOIN entspricht einem LEFT JOIN, nur, dass immer alle Datensätze der RIGHT-Tabelle im Resultset sind.

LEFT- und RIGHT JOINs sind OUTER JOINs. Manche RDBMS kennen auch noch einen FULL OUTER JOIN, bei dem alle Datensätze _beider_ Tabellen im Resultset sind. Wenn es wechselseitige Entsprechungen über die JOIN-Bedingung gibt, werden die Feldinhalte der jeweils anderen Tabelle dargestellt, wenn es keine Entsprechungen gibt, werden die nicht vorhandenen Feldinhalten der Felder der jeweiligen Tabelle als NULL-Werte dargestellt.

viele Grüße

Axel

[Thomas Mang]

Für Leute die es interessiert... ich hab selbst nach ner schlaflosen Nacht ne Lösung gefunden ;)

SELECT f.name, h.beschreibung, MAX(h.zeitpunkt)
FROM fall AS f, fall_historie AS h
WHERE
  f.id = h.fall_id
  AND $zeitpunkt >= h.zeitpunkt
GROUP BY h.fall_id

schmeisst mir zwar den zeitpunkt mit aus, aber der schadet auch nichts, und ich krieg jeweils die letzte Änderung vor dem angegebenen $zeitpunkt ausgegeben :)

interessant, was so alles geht ;)

[Axel Richter]

Hallo,

Für Leute die es interessiert... ich hab selbst nach ner schlaflosen Nacht ne Lösung gefunden ;)

SELECT f.name, h.beschreibung, MAX(h.zeitpunkt)
FROM fall AS f, fall_historie AS h
WHERE
  f.id = h.fall_id
  AND $zeitpunkt >= h.zeitpunkt
GROUP BY h.fall_id
interessant, was so alles geht ;)

Geht das wirklich?
Die GROUP BY - Anweisung gruppiert Datensätze der gejointen Datensatzmenge und fasst solche mit gleicher fall_id zu einem Datensatz zusammen. Dabei wird das Maximum von h.zeitpunkt innerhalb der zusammengefassten Datensätze ermittelt. Der Wert für f.name ist der, welcher der id des Gruppenkriteriums h.fall_id entspricht. Laut ANSI-SQL müsste, könnte hier auch, f.name mit im GROUP BY stehen. Soweit gut.

Welche Werte allerdings der gruppierte, zusammengefasste Datensatz für h.beschreibung hat, ist unbestimmt, da es ja in der Gruppe mehrere Datensätze mit unterschiedlichen Werten in h.beschreibung geben kann. Speziell muss es nicht so sein, dass der Wert für h.beschreibung der ist, welcher in dem Datensatz steht, der auch das Maximum von h.zeitpunkt innerhalb der zusammengefassten Datensätze enthält. Es kann im Prinzip jeder beliebige Wert für h.beschreibung innerhalb der zusammengefassten Datensätze sein.

viele Grüße

Axel

[Thomas Mang]

Welche Werte allerdings der gruppierte, zusammengefasste Datensatz für h.beschreibung hat, ist unbestimmt, da es ja in der Gruppe mehrere Datensätze mit unterschiedlichen Werten in h.beschreibung geben kann. Speziell muss es nicht so sein, dass der Wert für h.beschreibung der ist, welcher in dem Datensatz steht, der auch das Maximum von h.zeitpunkt innerhalb der zusammengefassten Datensätze enthält. Es kann im Prinzip jeder beliebige Wert für h.beschreibung innerhalb der zusammengefassten Datensätze sein.

Hmmm

OK.

Bei den paar Beispielen die ich testete ging es, aber das bedarf nun natürlich besonderer Beobachtung. Im Prinzip dachte ich, da der MAX(h.zetpunkt) eindeutig ist, bringt er mir auch den entsprechenden h.beschreibung eintrag...

muss testen ;)

bei meinem kleinen beispielen die ich jetzt so hatte gings, aber ... das kann natürlich dann auch zufall sein.

[Thomas Mang]

wenns kreuz und quer geht, gehts doch nicht :( völlig recht du hast

*schnüff*

ich sinniere mal weiter, aber was ich seit gestern durch durch und rein und rumlesen alles gelernt hab ich mal wieder unglaublich ;)

Grüsse
Thomas