(mysql) Abfrage mit berechneten Werten

[Susanne Jäger]

Hallo,

ich stoße gerade an die Grenzen meiner SQL-Fähigkeiten, vielleicht kann mir ja jemand helfen.

Tatort:
MySQL Datenbank zur Verwaltung von Videoaufnahmen.
Darin u.a.
* eine Tabelle cass(etten) mit den Feldern cassID, cassName, cassLaenge
* eine Tabelle aufnahmen mit u.a. den Feldern aufnahmeID, cassID, position, laenge

auf die Erfassung der Restlaufzeit in der Datenbank wollte ich verzichten ('keine computed values'), da ich dachte, dass die sich ja errechnen läßt.

Das schaffe ich aber nur jeweils für eine einzelne Kassette, indem ich nacheinander zwei Abfragen starte und PHP die Differenz ausrechnen lasse.

Was ich gerne hätte:
eine MySQL-Abfrage, die mir die Restlaufzeit aller Kassetten ermittelt um damit z.B. Abfragen wie "Wo ist denn noch mehr als 75min Platz" möglich zu machen.

mein letzer Versuch war:

"SELECT c.cassName,  c.cassLaenge,  a.laenge, (c.cassLaenge - a.laenge) AS restzeit FROM aufnahme a, cass c WHERE  c.cassID = a.cassID"

aber damit bekomme ich nur für jede einzelne Aufnahme die Differenz von Geamtlänge der Kassette und Länge der Aufnahme, aber nicht das Ergebnis für alle Aufnahmen mit der gleichen cassID.

Geht das überhaupt oder muss ich was an der Datenstruktur ändern? Als Datenbank-Anfängerin habe ich versucht mich streng an die Normalisierungsregeln zu halten, und es dabei vielleicht übertrieben?

Ich hoffe das Problem ist halbwegs klar geworden

Susanne

[Frank Jonas]

Hallo Susanne,

"SELECT c.cassName,  c.cassLaenge,  SUM(a.laenge) FROM aufnahme a, cass c WHERE  c.cassID = a.cassID GROUP BY c.cassName, c.cassLaenge"

sollte DIch Deinem Ziel zumindest etwas näher bringen. Da meine Erfahrungen mit MySQL ziemlich beschränkt sind und eine an dieser Stelle benötigte Unterabfrage meines Wissens nicht unterstützt wird, geht mir allerdings schon die Luft aus.

Hoffe, es hilft trotzdem ein wenig.

Gruß Frank

[Susanne Jäger]

Hallo Frank,

erstmal danke

sollte DIch Deinem Ziel zumindest etwas näher bringen.

tut es, jetzt kann ich immerhin schon mal eine nach Restlaufzeit sortierte Liste bekommen

SELECT c.cassName,  c.cassLaenge,  (c.cassLaenge - SUM(a.laenge)) AS restzeit FROM aufnahme a, cass c WHERE  c.cassID = a.cassID GROUP BY c.cassName, c.cassLaenge ORDER BY restzeit DESC

Da meine Erfahrungen mit MySQL ziemlich beschränkt sind und eine an dieser Stelle benötigte Unterabfrage meines Wissens nicht unterstützt wird, geht mir allerdings schon die Luft aus.

"Unterabfrage" gibt mir da doch mal wieder ein Stichwort zum Weitersuchen. Guido Stepken behauptet doch in seinem Mysql-Handbuch das sich subselects ganz einfach durch self-joins ersetzen lassen.
Das muss ich jetzt nur noch verstehen. ;-)

Gruß Susanne

[Daniela Koller]

Hi Susanne

sollte DIch Deinem Ziel zumindest etwas näher bringen.
tut es, jetzt kann ich immerhin schon mal eine nach Restlaufzeit sortierte Liste bekommen

und wenn du mit having arbeitest kannst du auch filtern wie du willst

Dafür brauchst du noch einen Group by c.cassName, c.cassLaenge
den brauchst du allerdings eh für die sum Funktion.

Deine Query sähe also so aus: Select c.cassName, c.cassLaenge, (c.cassLaenge - sum(a.laenge)) as restzeit
                               Group By c.cassName, c.cassLaenge
                               Having restzeit > was du willst

Ob erst Group By oder Having weis ich nicht mehr auswändig, deswegen bitte
rasch probieren und wenns n Syntaxfehler gibt drehen.

Das ganze hat glaub ich allerdings ein Problem, es setzt voraus das
die einzelnen Stücke nahtlos aneinander passen, sobald eine Pause
dazwischen ist wird die nicht berücksichtigt.

SELECT c.cassName,  c.cassLaenge,  (c.cassLaenge - SUM(a.laenge)) AS restzeit FROM aufnahme a, cass c WHERE  c.cassID = a.cassID GROUP BY c.cassName, c.cassLaenge ORDER BY restzeit DESC

"Unterabfrage" gibt mir da doch mal wieder ein Stichwort zum Weitersuchen. Guido Stepken behauptet doch in seinem Mysql-Handbuch das sich subselects ganz einfach durch self-joins ersetzen lassen.
Das muss ich jetzt nur noch verstehen. ;-)

Schön wäre es wenns so einfach ist, gerade bei deiner Anwendung ist es
sehr schwer und je nach Anwendungsfall (deiner sieht danach aus, zumindest
ist mir noch nichts eingefallen, heisst aber nicht viel) sogar unmöglich.

Gruss Daniela

[Daniela Koller]

Hi Susanne

Deine Query sähe also so aus: Select c.cassName, c.cassLaenge, (c.cassLaenge - sum(a.laenge)) as restzeit
                               Group By c.cassName, c.cassLaenge
                               Having restzeit > was du willst

Sorry, natürlich muss dein from und where nochmal rein und das group by
hattest du ja schon, habs übersehen.

Also so:

select c.cassName, c.cassLaenge, (c.cassLaenge - sum(a.laenge)) as restzeit
  FROM aufnahme a, cass c
  WHERE c.cassID = a.cassID
  Group By c.cassName, c.cassLaenge
  Having restzeit > was du willst
  order by restzeit desc

Bei älteren MySQL Versionen solltest du das noch zu nem
inner join optimieren weil die das sonst sehr aufwändig
ausführen. Bei den neuen Versionen ist das jedoch behoben.

Das sähe so aus:

select c.cassName, c.cassLaenge, (c.cassLaenge - sum(a.laenge)) as restzeit
  FROM aufnahme a
       INNER JOIN cass c ON (c.cassID = a.cassID)
  Group By c.cassName, c.cassLaenge
  Having restzeit > was du willst
  order by restzeit desc

Gruss Daniela

[Susanne Jäger]

Hallo Daniela,

Also so:

select c.cassName, c.cassLaenge, (c.cassLaenge - sum(a.laenge)) as restzeit
  FROM aufnahme a, cass c
  WHERE c.cassID = a.cassID
  Group By c.cassName, c.cassLaenge
  Having restzeit > was du willst
  order by restzeit desc

Funktioniert wuderbar, vielen Dank.
Sind damit die Einschränkungen aus deiner ersten Nachricht ('Daten am Stück', 'subselects durch join ersetzen hier unmöglich') hinfällig?

Ich muß gestehen, mir SQL-Befehle noch wie 'ne Fremdsprache vorkommen, bei der ich mit Wörterbuch in der Hand jedes Wort einzeln übersetze und dann den Satzzusammenhang nicht hinkriege, aber ich hoffe das gibt sich noch.

Also noch mal tausend Dank
Susanne

[Daniela Koller]

Hi Susanne

Funktioniert wuderbar, vielen Dank.
Sind damit die Einschränkungen aus deiner ersten Nachricht ('Daten am Stück', 'subselects durch join ersetzen hier unmöglich') hinfällig?

Nein, das ist genau das selbe, nur syntaktisch falsch, ob die Einschränkung
hinfällig ist weist nur du aufgrund der Daten. Sie ist dann hinfällig
wenn gilt:
pos des Vorgängerliedes + länge des Vorgängerliedes = pos des Folgeliedes
also wenn da nicht noch ein paar Sekunden rein kommen.

Gruss Daniela

[Michael Schröpl]

Hi Susanne,

Ich muß gestehen, mir SQL-Befehle noch wie 'ne Fremdsprache vorkommen,
bei der ich mit Wörterbuch in der Hand jedes Wort einzeln übersetze und
dann den Satzzusammenhang nicht hinkriege, aber ich hoffe das gibt sich
noch.

hast Du in der Schule Mengenlehre gehabt? Das hilft m. E. enorm bei SQL.

Wenn Du mal die Begriffswelt im Kopf hast, wirst Du mit der Schreibweise
deutlich weniger Schwierigkeiten haben - und die Syntax kann man ja im
Handbuch nachlesen.

Viele Grüße
      Michael

[Susanne Jäger]

Hallo Michael,

hast Du in der Schule Mengenlehre gehabt? Das hilft m. E. enorm bei SQL.

hmm, da war mal was, ist das jetzt 30 oder erst 25 Jahre her. Aber vielleicht sollte ich mir das wirklich mal als Ansatz zu Herzen nehmen. klingt irgendwie einleuchtend, so mit alle aus einer tabelle und dann Teilmengen ... jo könnte gehen.
Wie ich in das Modell allerdings Joins und Groups und das heute gelernte Having einbaue ... darüber muß ich wohl noch mal in Ruhe nachdenken.

Wenn Du mal die Begriffswelt im Kopf hast, wirst Du mit der Schreibweise
deutlich weniger Schwierigkeiten haben - und die Syntax kann man ja im
Handbuch nachlesen.

Na klar Syntax ist bei mir ganz lange was zum Nachschlagen (oder auch draufloshacken und gucken was passiert). Und irgendwann platzt auch erfahrungsgemäß der Knoten und die einzelnen Bruchstücke passen auf einmal. (obs nun spanisch oder SQL ist). Nur bis dahin komm ich mir immer so unendlich 'dumm' vor. und das mag ich ja nun gar nicht.

Aber jetzt driftets endgültig ab

Gruß Susanne