MYSQL Query: NOT IN? "JOIN"+"WHERE"-clause?

[max]

Hallo,
komme mittlerweile absolut nicht mehr weiter, wäre super wenn Jemand helfen könnte!

Ich will eine zusätzliche Bedingung in meinen query einbauen. Der soll mir alle Objekte aus objects ausgeben, für die zu einem gegebenen Datum ($beg,$end) kein Eintrag in bookings vorhanden ist (keine Buchung vorliegt):

$query = "SELECT nr FROM objects WHERE nr NOT IN  
  (  
     SELECT objnr FROM bookings WHERE  
     (('$end' > date1) AND ('$end' <= date2))  
     OR  
     (('$beg' < date2) AND ('$beg' >= date1))  
     OR  
     (('$beg' <= date1) AND ('$end' >= date2))  
  )  
";

Ergebnis: mysql_affected_rows()=0, obwohl zutreffende Datensätze vorhanden.

Beliebige Kombinationen mit DISTINCT und NOT EXISTS brachten auch keinen Erfolg. Der geschachtelte Query funktioniert, das weiss ich sicher.

Habe noch den Tipp bekommen das Ganze mit JOIN zu machen, nur wie kombiniere ich JOIN mit meiner zusätzlichen Bedingung? Oder ist ein JOIN gar ncht nötig?

Danke!
Max

[Vinzenz Mai]

Hallo,

Ich will eine zusätzliche Bedingung in meinen query einbauen. Der soll mir alle Objekte aus objects ausgeben, für die zu einem gegebenen Datum ($beg,$end) kein Eintrag in bookings vorhanden ist (keine Buchung vorliegt):

es ist *nie* eine gute Idee, PHP-Code zu posten, wenn man ein SQL-Problem hat.
Es ist dagegen eine hervorragende Idee, mit dem Client der Wahl zunächst eine statische Abfrage zu erstellen, die das gewünschte Ergebnis liefert - und *erst dann* die Abfrage dynamisch mit $programmiersprache zusammenzubauen.

$query = "SELECT nr FROM objects WHERE nr NOT IN

(
     SELECT objnr FROM bookings WHERE
     (('$end' > date1) AND ('$end' <= date2))
     OR
     (('$beg' < date2) AND ('$beg' >= date1))
     OR
     (('$beg' <= date1) AND ('$end' >= date2))
  )
";

  

> Habe noch den Tipp bekommen das Ganze mit JOIN zu machen, nur wie kombiniere ich JOIN mit meiner zusätzlichen Bedingung? Oder ist ein JOIN gar ncht nötig?  
  
Wie üblich ein LEFT JOIN mit Prüfung auf NULL-Werte. Allerdings fällt dieser bei Deinem Problem komplizierter als die Subquery, da Deine Einschränkungen alle in die Join-Bedingung müssen.  
  
Kommen wir nun zum Kern des Problems, das ich mit Deiner Frage habe:  
  
$beg soll einen Startwert darstellen, $end einen Endwert. Was diese genau bedeuten, erschließt sich mir nicht. Unter den Spalten date1 und date2 kann ich mir gar nichts vorstellen, genauswenig wieso Deine drei über OR verknüpften Bedingungen Dir das gewünschte liefern. Bitte poste daher Beispieldaten:  
  
Beispieldaten für die Werte, die in $beg und $end enthalten sein können,  
Beispieldaten für objects, dazu passende für bookings - und erläutere, was das aufgrund dieser Beispieldaten gewünschte Ergebnis der Abfrage ist.  
  
Eine Idee hätte ich noch zu PHP:  
Lass Dir das generierte Statement anzeigen. Es könnte sein, dass $beg und/oder $end nicht das enthalten, was Du vermutest. Poste das erzeugte Statement.  
  
Ach ja, es ist eine sehr gute Idee, Daten, die von außerhalb kommen in einem SQL-Statement gemäß DBMS zu behandeln, z.B. mit mysql(i)\_real\_escape\_string().  
  
  
Freundliche Grüße  
  
Vinzenz

[max]

Herzlichen Dank für deine Antwort! Zu deinen Fragen:

es ist *nie* eine gute Idee, PHP-Code zu posten, wenn man ein SQL-Problem hat.
Es ist dagegen eine hervorragende Idee, mit dem Client der Wahl zunächst eine statische Abfrage zu erstellen, die das gewünschte Ergebnis liefert - und *erst dann* die Abfrage dynamisch mit $programmiersprache zusammenzubauen.

Habe ich quasi getan, die Variablen, die in meinem Query vorkommen habe ich zum "Basteln" fest definiert und den Query isoliert.

Wie üblich ein LEFT JOIN mit Prüfung auf NULL-Werte. Allerdings fällt dieser bei Deinem Problem komplizierter als die Subquery, da Deine Einschränkungen alle in die Join-Bedingung müssen.

Das ist mein Problem, ja ;)

$beg soll einen Startwert darstellen, $end einen Endwert. Was diese genau bedeuten, erschließt sich mir nicht. Unter den Spalten date1 und date2 kann ich mir gar nichts vorstellen, genauswenig wieso Deine drei über OR verknüpften Bedingungen Dir das gewünschte liefern. Bitte poste daher Beispieldaten:

Beispieldaten für die Werte, die in $beg und $end enthalten sein können,
Beispieldaten für objects, dazu passende für bookings - und erläutere, was das aufgrund dieser Beispieldaten gewünschte Ergebnis der Abfrage ist.

Es geht um Buchungen. $beg ist das Anfangsdatum (erster Tag der Vermietung) und $end das Enddatum.

zb.:

(('$end' > date1) AND ('$end' <= date2))

Enddatum der Eingabe zwischen Anfangs (date1)- und Enddatum (date2) einer bereits bestehenden Buchung? -> Kollision
Rest analog

Ein mögliches object: Nr, Name, beschreibung
eine mögliche Buchung: Name des Kunden, gebuchtes Objekt, Anfangsdatum, Enddatum

Um nun für eine neue Buchung die Verfügbarkeiten zu prüfen, will ich alle objects haben, die für den neu angefragten Zeitraum noch frei sind.

Bsp: Anfrage für Buchung vom 10.12.08 - 12.12.08. Ausgabe soll sein: Alle objects, für die in diesem Zeitraum noch keine Buchung in bookings eingetragen ist.

$beg="20081210"; $end="20081212";
  $query = "SELECT nr FROM objects WHERE nr NOT IN
  (
     SELECT objnr FROM bookings WHERE
     (('$end' > date1) AND ('$end' <= date2))
     OR
     (('$beg' < date2) AND ('$beg' >= date1))
     OR
     (('$beg' <= date1) AND ('$end' >= date2))
  )
";

>   
>   
  
  

> Eine Idee hätte ich noch zu PHP:  
> Lass Dir das generierte Statement anzeigen. Es könnte sein, dass $beg und/oder $end nicht das enthalten, was Du vermutest. Poste das erzeugte Statement.  
  
S.o., schon abgeklärt. Das Statement:  
SELECT nr FROM objects WHERE nr NOT IN ( SELECT objnr FROM bookings WHERE (('20081212' > date1) AND ('20081212' <= date2)) OR (('20081210' < date2) AND ('20081210' >= date1)) OR (('20081210' <= date1) AND ('20081212' >= date2)) )  
  

> Ach ja, es ist eine sehr gute Idee, Daten, die von außerhalb kommen in einem SQL-Statement gemäß DBMS zu behandeln, z.B. mit mysql(i)\_real\_escape\_string().  
  
Variablen sind sauber, die Prüfung erfolgt vorneweg, bei der Eingabe, weil ich die Info da schon brauche.  
  
Grüße,  
Max

[Rouven]

Hello,

SELECT nr FROM objects WHERE nr NOT IN ( SELECT objnr FROM bookings WHERE (('20081212' > date1) AND ('20081212' <= date2)) OR (('20081210' < date2) AND ('20081210' >= date1)) OR (('20081210' <= date1) AND ('20081212' >= date2)) )

was für einen Datentyp haben denn deine Date-Spalten? Das Stringformat erscheint mir erstens suboptimal und ist zweitens eine häufige Erklärung für Probleme mit Abfragen.

MfG
Rouven

[max]

Hey,

was für einen Datentyp haben denn deine Date-Spalten? Das Stringformat erscheint mir erstens suboptimal und ist zweitens eine häufige Erklärung für Probleme mit Abfragen.

sind date-Spalten. Da die innere Anfrage funktioniert, und das weiss ich sicher(!), habe ich mir keine Gedanken drüber gemacht, ob ich die Hochkommata brauche oder nicht. Falls nein funktioniert es aber mit trotzdem.
Das Problem ist die NOT IN Bedingung bzw ein möglicher JOIN, nicht die innere Abfrage!

Grüße,
Max

[Ilja]

yo,

die klammern sind nicht wirklich hilfreich, in deinem falle und verwirren mehr, als sie helfen, da AND stärker bindet als OR. ausserdem finde ich die formatierung schwer zu lesen.

SELECT nr
FROM objects
WHERE nr NOT IN (SELECT objnr
                 FROM bookings
                 WHERE date1 < '20081212' AND date2 >= '20081212'
                 OR date1 <= '20081210' AND date2 > '20081210'
                 OR date1 >= '20081210' AND date2 <= '20081212'
                )
;

wo sind die beispieldaten, die Vinz ganz zurecht gefordert hat ?

Ilja

[max]

Thx für deine Antwort

die klammern sind nicht wirklich hilfreich, in deinem falle und verwirren mehr, als sie helfen, da AND stärker bindet als OR. ausserdem finde ich die formatierung schwer zu lesen.

SELECT nr
FROM objects
WHERE nr NOT IN (SELECT objnr
                 FROM bookings
                 WHERE date1 < '20081212' AND date2 >= '20081212'
                 OR date1 <= '20081210' AND date2 > '20081210'
                 OR date1 >= '20081210' AND date2 <= '20081212'
                )
;

wie gesagt, die innere Anfrage funktioniert so wie sie soll, das weiss ich sicher (weil Sie vorher alleine stand). Ich klammer' halt immer ganz gerne, zum Schutz gegen injections, aber in dem Fall unnötig, stimmt.
Es geht mir hier allein darum, wie ich den bereits funktionierenden query mit der NOT IN oder eben einem LEFT JOIN verbinden kann.

wo sind die beispieldaten, die Vinz ganz zurecht gefordert hat ?

Hab ich euch doch schon geschrieben?! Nochmal ausführlich:

Ein mögliches object: Nr 4, Name: BMW X5, beschreibung: super schnell
eine mögliche Buchung: Name des Kunden: Max Mustermann, gebuchtes Objekt: ?, Anfangsdatum: 20081210, Enddatum:20081212

Um zu bestimmen welches Objekt der Kunde überhaupt buchen kann, muss ich herausfinden, zu welchen Objekten für den gegebenen Zeitraum noch keine Buchung vorliegt.
Ist das was du meintest?

Grüße,
Max

[Ilja]

yo,

Hab ich euch doch schon geschrieben?! Nochmal ausführlich:

Ein mögliches object: Nr 4, Name: BMW X5, beschreibung: super schnell
eine mögliche Buchung: Name des Kunden: Max Mustermann, gebuchtes Objekt: ?, Anfangsdatum: 20081210, Enddatum:20081212

wenn ich dein problem richtig verstanden habe, dann bekommst du mit deiner abfrage keine datensätze zurück. dann gibt es zwei möglichkeiten, die abfrage ist falsch oder sie ist richtig und die daten führen einfach zu keinen ergebnis.

da die abfrage so trivial ist, hast du dich entweder mit den zeiträumen geirrt, die du in den bedinungen haben willst oder aber die daten geben einfach keinen treffer her.

mit deinem beispiel jedenfalls können wir nichts anfangen, da dieser datensatz in der unterabfrage erfasst werden würde, also nicht angezeigt wird. was wir bräuchten wäre ein beispiel, das angezeigt werden sollte, wobei ich davon ausgehe, dass:

date1 = Anfangsdatum
date2 = Enddatum

Ilja

[max]

wenn ich dein problem richtig verstanden habe, dann bekommst du mit deiner abfrage keine datensätze zurück. dann gibt es zwei möglichkeiten, die abfrage ist falsch oder sie ist richtig und die daten führen einfach zu keinen ergebnis.

genau, ich bekomme keine Daten zurück. Sollte ich aber. Deshalb versuche ich rauszufinden was an der Abfrage falsch ist.

da die abfrage so trivial ist, hast du dich entweder mit den zeiträumen geirrt, die du in den bedinungen haben willst oder aber die daten geben einfach keinen treffer her.

  
SELECT objnr  
FROM bookings  
WHERE date1 < '20081212' AND date2 >= '20081212'  
OR date1 <= '20081210' AND date2 > '20081210'  
OR date1 >= '20081210' AND date2 <= '20081212'  

gibt mir alle bereits vorhandenen Buchungen für den angefragten Zeitraum zurück, die Anfrage funktioniert also. Ich möchte sie aber umkehren, also die NICHT gebuchten Objekte haben. Bisher habe ich das außerhalb des Querys über ein Array gelöst. Hat funktioniert, ist aber unnötig kompliziert.

mit deinem beispiel jedenfalls können wir nichts anfangen, da dieser datensatz in der unterabfrage erfasst werden würde, also nicht angezeigt wird. was wir bräuchten wäre ein beispiel, das angezeigt werden sollte, wobei ich davon ausgehe, dass:

date1 = Anfangsdatum
date2 = Enddatum

Verstehe jetzt nicht ganz was du meinst. Die Unterabfrage ist ja genau mein Problem?! Vllt. hilft ein vollständiges Beispiel:

objects:

nr   |  name  |   beschreibung

1      Audi         toll
   2      BMW        ganz toll

bookings:

date1     |     date2     |      name       |  objnr

2008-12-08      2008-12-10     Horst Schlaemmer    1
 2008-12-10      2008-12-12      Max Mustermann     2

Anfrage: A.Merkel will ein Auto im Zeitraum 20081210 bis 20081212. Es soll also angezeigt werden: "Verfügbare Autos: Nr1: Audi"

Jetzt klar? ;)

Grüße,
Max

[Ilja]

yo,

gibt mir alle bereits vorhandenen Buchungen für den angefragten Zeitraum zurück, die Anfrage funktioniert also. Ich möchte sie aber umkehren, also die NICHT gebuchten Objekte haben. Bisher habe ich das außerhalb des Querys über ein Array gelöst. Hat funktioniert, ist aber unnötig kompliziert.

ich bleibe bei meiner aussage, wenn die unterabfrage funktioniert, dann ist die umkehrung richtig in der abfrage, die du gemacht hast. die abfrage/umkerung ist so trivial, da kann man fast nichts falsch machen.

ergo bleiben eigentlich nur zwei möglichkeiten, entweder die daten sind der knackpunkt oder aber die unterabfrage funktioniert nicht so, wie du glaubst. mehr möglichkeiten sehe ich im moment nicht.

aber mir ist klarer geworden, was du willst. und deine unterabfrage ist wirklich unvorteilhaft. ob zeiträume überschneidungen haben läßt sich eleganter prüfen, nämlich das startdatum mit dem jeweiligen enddatum vergleichen

• date1 und date2 geben den zeitraum der buchungstabelle wieder
• anfragestart und anfrageende ist der zeitraum der anfrage

dann liegt eine überschneidung der zeiträume vor wenn:

1. date2 >= anfragestart AND date1 <= anfrageende

sieht doch wesentlich leichter aus als deine drei OR bedingungen, zumal ich auch da einen fehler vermute, weil du nicht immer die gleichheit der datumswert mit rein nimmst.

Ilja

[max]

ich bleibe bei meiner aussage, wenn die unterabfrage funktioniert, dann ist die umkehrung richtig in der abfrage, die du gemacht hast. die abfrage/umkerung ist so trivial, da kann man fast nichts falsch machen.

ich kann dir nur sagen, dass die unterabfrage das gewünschte ergebnis liefert, wenn ich sie isoliere. der fehler muss woanders liegen.

1. date2 >= anfragestart AND date1 <= anfrageende

sieht doch wesentlich leichter aus als deine drei OR bedingungen, zumal ich auch da einen fehler vermute, weil du nicht immer die gleichheit der datumswert mit rein nimmst.

ich weiss... absicht, zur entwicklung. das mit dem gleichheitszeichen muss in dem ebenfalls so sein, da erneute vermietung am tag der abgabe möglich ist.

ich muss langsam zu potte kommen. ich lass es jetzt so wie's war, mit ein paar zeilen mehr code, und kümmer mich am ende nochmal drum, wenn ich zeit hab.
danke für deine hilfe!
grüße,
max

[Ilja]

yo,

ich weiss... absicht, zur entwicklung. das mit dem gleichheitszeichen muss in dem ebenfalls so sein, da erneute vermietung am tag der abgabe möglich ist.

da gehst du aber meiner meinung nach den falschen weg, wobei ich natürlich nicht genau deine fachlickeit kenne. ich würde in diesem falle aber die uhrzeit mit ins spiel nehmen. wenn jemand ein auto erst spät abends zurück geben will und jemand anderes das auch am gleichen tag früh morgens buchen will, dann sollte das auto eigentlich nicht zur verfügung stehen.

ich muss langsam zu potte kommen. ich lass es jetzt so wie's war, mit ein paar zeilen mehr code, und kümmer mich am ende nochmal drum, wenn ich zeit hab.

wie gesagt, was wir brauchens ist eigentlich nur geeignete beispieldaten. da du immer sagst, die unterabfrage funktioniert, zeige uns doch zwei beispieldatensätze, wo die unterabfrage alleine einen der beiden richtigerweise trifft und wo dann die ganze abfrage bei den gleichen datnesätze nicht mehr funktioniert. am besten sowohl mit deinen drei bedingungen und mit meiner version.

Ilja