schwierige Abfrage MySQL

[Tobias Hahner]

Hallihallo!

Ich stehe gerade ein Wenig auf dem Schlauch mit einer Datenbankabfrage in MySQL 5.0.67 auf MacOSX 10.6.

Und zwar habe ich folgende Tabellen:

vehicles

• id int 11 auto_increment PRIMARY KEY
• name varchar(20)
• ... weitere unwichtige eigenschaften ...

trips

• id int 11 auto_increment PRIMARY KEY
• starttime datetime
• destinationid id unsigned (ID eines in einer anderen Tabelle gehaltenen Ortes)

vehicles2trips

• id int 11 auto_increment PRIMARY KEY
• vehicleid int 10 unsigned
• tripid int 10 unsigned
  (Mir ist klar, dass ich hier eigentlich keine Spalte "id" brauche, aber ich denke nicht, dass diese Spalte Schaden anrichtet ;) )

Es werden also in der Tabelle "vehicles" verschiedene Fahrzeuge (Fuhrpark, also Zugmaschinen und Anhänger) verwaltet, und in der Tabelle "trips" werden geplante Fahrten dieser Fahrzeuge eingetragen.
Da zu einem Trip theoretisch ein Zugfahrzeug mit einem Anhänger gehören können, gibt es die Zuordnungstabelle "vehicles2trips"…

Zum tieferen Verständnis:
Ein "Trip" zeichnet sich, wie in der Datenstruktur schon ersichtlich ist, nur durch 2 Eigenschaften aus: den Zeitpunkt der Abfahrt, und den Bestimmungsort. Da es hier nur um näherungsweise Planung geht, ist dieses vereinfachte Modell aus der Praxis heraus vollkommen ausreichend…

Nun zu meinem eigentlichen Problem:
Um den Aufenthaltsort eines Fahrzeugs (definiert durch seine ID) zum einem bestimmten Zeitpunkt "giventime" zu erfahren, möchte ich nun Folgendes von der Datenbank:

"Gib mir den letzten 'trip', dessen starttime kleiner ist als "giventime", und der in 'vehicles2trips' mit "given_vehicleid" verknüpft ist."

Mein erster Ansatz war:

• gib mir alle Tripids zur gegebenen vehicleid
• gehe alle entsprechenden Trips in einer Schleife durch und merke dir den richtigen.
  Dieser Ansatz war: 1) in PHP schnell gebaut, 2) suboptimal, weil zigtausend Querys an MySQL gehen...

Der nächste Ansatz war:

  
SELECT  
   vehicles2trips.tripid as tripid,  
   trips.destinationid as destinationid,    //(DIESEN WERT SUCHE ICH HAUPTSÄCHLICH!)  
FROM  
   vehicles2trips  
WHERE  
   vehicles2trips.vehicleid = /*givenvehicleid*/  
LEFT JOIN  
   trips  
ON  
   trips.id = vehicles2trips.tripid  

Damit (die Abfrage ist jetzt aus dem Kopf, weil das Original viel grösser, aber prinzipiell genauso ist) bekomme ich zumindest ALLE trips, die zu dem gesuchten vehicle "gehören".
Ich müsste aber in einer Schleife nun per PHP denjenigen Datensatz raussuchen, der die Bedingung "der letzte vor dem gegebenen Datum" erfüllt.
Machbar, aber doof…

Frage ist nun:
Wie kann ich den (/das? Aufklärung erbeten ;) ) Query so erweitern, dass MySQL mit seinen Datums-und Zeitfunktionen die Filterarbeit übernimmt und mir von vornherein nur DEN EINEN Datensatz zurückgibt, den ich suche?
Schliesslich möchte ich mit PHP diesen Datensatz nur auswerten, es ist nicht PHPs Job, sich den Datensatz erst zu suchen. Das ist Job von MySQL…

Ich bin für alle Lösungsansätze, auch Hinweise auf suboptimale Datenhaltung, dankbar...

Beste Grüße,
Tobias Hahner