Disjunktion in SQL

[Jesus_666]

Ich schreibe gerade an einer Kundenverwaltungssoftware für eine monatlich abzurechnennde Dienstleistung. Für das Abrechnungsmodul müssen wir wissen, für welche Kunden noch Rechnungen ausstehen. Konkret brauche ich eine Liste aller Kunde/Monat-Paare, in denen für den Kunden keine Rechnung vorhanden ist.

Was ich habe:

Code:

Tabelle `contracts`:
    int `customer_id`
    date `delivery_date` // Wann das Produkt geliefert wurde; ab hier wird abgerechnet
    date `termination_date` // Wann der Vertrag gekündigt wurde; kann NULL sein
   < weitere, hier unwichtige Felder>

Tabelle `invoices`:
    varchar `id`
    int `customer_id`
    date `date`
    <weitere, hier unwichtige Felder>

Tabelle `dates`: // Enthält eine monatsweise Liste aller Daten zwischen dem 1.1.2010 und dem 1.12.2059
    date `date`

Der Einfachheit halber gehen wir davon aus, daß Kunden immer am Ersten eines Monats abgerechnet werden. Jeder Kunde hat exakt einen Vertrag.

Ich habe versucht, zu joinen und dann per WHERE abzufragen, wo für die Rechnungen nur NULL steht, aber damit komme ich auf keinen grünen Ast. Letztenendes brauche ich alle Zeilen aus `dates`, die nicht in `invoices` erwähnt werden, bis auf die, die außerhalb der Spanne `delivery_date`-`termination_date` liegen, aber ich kome nicht darauf, wie ich da rankomme.

Hat jemand eine sinnvolle Idee? Ich nehm' auch gerne eine, die in eine ganz andere Richtung geht, solange sie tut, was ich will, und nicht den ganzen Server lahmlegt.


Ach ja, falls der Dialekt relevant wird: Der Kram läuft auf MySQL 5.0.

[Xardas der Dunkle]

Also in meinem kleinen Test hat das funktioniert ... ich lege aber keine Hand dafür ins Feuer das es das auch bei der echten Tabelle tut.

Code:

    SELECT
                customer_id,
                date,
                (
                    SELECT      COUNT(date)
                    FROM        invoices
                    WHERE       invoices.customer_id = contracts.customer_id
                    AND         DATE_FORMAT(invoices.date, \'%Y%m\') = DATE_FORMAT(dates.date, \'%Y%m\')
                    GROUP BY    date
                ) > 0 AS has_invoice
    FROM
                contracts
    INNER JOIN
                dates ON (
                    dates.date >= delivery_date AND (
                        termination_date IS NULL OR dates.date <= termination_date
                ))

[Jesus_666]

Okay, das sieht schon mal nicht schlecht aus. Ich komme vor Dienstag nicht ins Büro, aber dann werde ich das mal versuchen. Danke.

[DFYX]

Ich habe versucht, zu joinen und dann per WHERE abzufragen, wo für die Rechnungen nur NULL steht, aber damit komme ich auf keinen grünen Ast.

...

Nur kurz ins Blaue geraten: du bedenkst, dass eine Prüfung mit WHERE whatever = NULL nie true oder false gibt, sondern immer maybe? Also selbst WHERE NULL = NULL. Richtig wäre WHERE whatever IS NULL. Könnte sein, dass das dein Problem war. Ansonsten würde ich was ähnliches empfehlen, wie Xardas:

Code (SQL):

SELECT ... FROM contracts INNER JOIN ... WHERE (SELECT COUNT(date) FROM invoices WHERE ...) = 0

Edit: ja, ich weiß, ich bin spät dran, aber vielleicht hilfts ja doch noch.

[Jesus_666]

Momentan läuft's auf einer modifizierten Version von Xardas' Query.

Code (SQL):

SELECT
                customer_id,
                date,
                (
                    SELECT      COUNT(date)
                    FROM        invoices
                    WHERE       invoices.customer_id = contracts.customer_id
                    AND         DATE_FORMAT(invoices.date, '%Y%m') = DATE_FORMAT(dates.date, '%Y%m')
                    GROUP BY    date
                ) > 0 AS has_invoice
    FROM
                contracts
    INNER JOIN
                dates ON (
                    dates.date >= delivery_date AND (
                        termination_date IS NULL OR dates.date <= termination_date
                ))
    WHERE date <= NOW()

Ich werde' mir das später noch mal ansehen, ob es sich nicht noch optimieren läßt, aber für's Erste läuft es und ich habe dringendere Probleme (sprich: Wartung unseres nagelneuen Legacy-Systems).