SQL-Abfrage über mehrere Tabellen

[mkinzler]

Kann es zu einem Auftrag mehrere Stati geben?

[buyden]

Nein, der Status eines Auftrages wird immer wenn eine neue Aktion vorgenommen wird, geändert.
Allerdings wird in der Statistiktabelle ein Eintrag erstellt, wenn die letzte Statusänderung an einem anderen Tag war. Das heißt also wird ein Auftrag an einem Tag begonnen und abgeschlossen hat er nur einen Statistikeintrag, egal wie oft der Status geändert wurde. Wird er an einem anderen Tag abgeschlossen oder bearbeitet (Statusänderung) wird ein neuer Statistikeintrag erstellt.
Fazit:
In der Statistiktabelle kann es zu einem Auftrag mehrere Einträge mit unterschiedlichen Daten und Statuscodes geben.
Entscheidend für die Erinnerung ist aber immer nur der letzte Eintrag zu einem Auftrag.

[buyden]

ich hab schon ne Erinnerung drin mit einer festen Frist (Codes werden noch aus ner Datei ausgelesen und die Frist aus meiner config.ini) und hab das so gelöst:

Delphi-Quellcode:

with frmdata.quremind do
      begin
        close;
        sql.Clear;
        sql.Add('SELECT * FROM auftrag LEFT OUTER JOIN geraete ON auftrag.geraet = geraete.geraeteid WHERE status IN('+remindstatus.commatext+') AND annahmedatum <= '''+formatdatetime('yyyy-mm-dd',date-config.ReadInteger('Grundeinstellungen','Reminddays',0)) +''' AND techniker= '+config.ReadString('Grundeinstellungen','Technikernummer','')+'; ');
        open;
        if recordcount>0 then
          begin
            if messagedlg('Es wurden '+ inttostr(recordcount)+ ' offene Aufträge gefunden, die älter als '+ inttostr(config.ReadInteger('Grundeinstellungen', 'Reminddays',14))+ ' Tage sind. Möchten Sie die Aufträge jetzt ansehen?', mtconfirmation, [mbyes,mbno],0)=mryes then
              begin
                frmremind.Show;
              end;
          end;
      end;

Aber wie gesagt: verschiedene Statuscodes mit verschiedenen Fristen ist das Ziel.

[omata]

Sorry, aber davon kann ich leider nicht ausgehen...

Zitat von buyden:

Gehen wir also davon aus dass statistik.auftrag der Fremdschlüssel zu auftrag.auftragid ist und somit ne n:1-Beziehnung besteht.

weil statistik.auftrag vom Typ VARCHAR ist und auftrag.auftragid ein BIGINT ist.

Das geht so nicht, oder etwa doch? Ist ja MySQL, da weiss man nie.

Edit...
So habe mir nochmal Gedanken gemacht...

Warum ist bei dir die Auftragsnummer nicht in der Auftragstabelle? Die sollte sich doch nicht pro Status ändern können oder?

Folgende Tabellenstruktur würde ich vorschlagen:

SQL-Code:

CREATE TABLE `auftrag` (
  `auftragID` int(10) unsigned NOT NULL auto_increment,
  `auftragnummer` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`auftragID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `status` (
  `StatusID` int(10) unsigned NOT NULL auto_increment,
  `TagFrist` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`StatusID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `auftrag_status` (
  `StatistikID` int(10) unsigned NOT NULL auto_increment,
  `AuftragID` int(10) unsigned NOT NULL default '0',
  `StatusID` int(10) unsigned NOT NULL default '0',
  `Techniker` varchar(10) NOT NULL default '',
  `Datum` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`StatistikID`),
  KEY `FK_auftrag_status_1` (`AuftragID`),
  KEY `FK_auftrag_status_2` (`StatusID`),
  CONSTRAINT `FK_auftrag_status_2` FOREIGN KEY (`StatusID`) REFERENCES `status` (`StatusID`),
  CONSTRAINT `FK_auftrag_status_1` FOREIGN KEY (`AuftragID`) REFERENCES `auftrag` (`auftragID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Wenn du in das Datum als Default einen Timestamp machen lässt, wird automatisch das aktuelle Datum+Zeit in die Spalte Datum geschrieben, wenn ein Datensatz in die Datenbank eingetragen wird.
Dann brauchst du das nicht in deiner Anwendung beachten.

Mit folgender Anweisung kommst du dann an die letzte Auftragsbearbeitung, wenn die Frist, die sich auf den aktuellsten Statuscode bezieht, den aktuellen Tag überschritten hat. So hat man die ganze Geschichte eines Auftrags in der Datenbank, da brauch kein Eintrag geändert zu werden, wenn schon am selben Tag ein Datensatz besteht.

SQL-Code:

SELECT *
FROM auftrag a
INNER JOIN auftrag_status x
  ON a.auftragID = x.auftragID
INNER JOIN status s
  ON x.statusID = s.statusID
WHERE datum = (SELECT MAX(datum)
               FROM auftrag_status
               WHERE auftragID = a.auftragID)
  AND DATE(NOW()) > DATE(DATE_ADD(datum, INTERVAL tagfrist DAY))

Ich habe das mit MySQL5 getestet. Sollte aber auch mit 4.1 gehen.

Gruss
Thorsten

[buyden]

ich hab schon mitbekommen, dass ich meine DB noch ein bisschen umbauen muss.

Also die auftragsnummer gibt es in der Auftragstabelle (auftrag.nummer(varchar)), das ist im Prinzip der Name des Auftrags aber eben nicht der Primärschlüssel, der ist auftragid. In der Statistiktabelle muss ich dann richtigerweise noch das Feld ändern wenn die Tabellen verknüpft werden. Im Moment wird die Statistiktabelle von ner Prozedur befüllt und ich hab da die Auftragsnummer statt der auftragsid genommen um meine alte Abfrage einfacher zu machen.
Dann hab ich festgestellt, dass ich gar keine ForeignKeys anlegen kann weil ich MyISAM als Typ habe. Ich hab schon versucht, das umzuwandeln, funktioniert aber nicht. Gibt's da noch nen Trick oder muss ich die DB komplett neu anlegen und dann die Daten kopieren?

ich hab das ganze mal mit anpassungenan die unveränderte struktur im querybrowser getestet mit dem resultat das mir der ganze rechner eingeschlafen ist weil der mysqlserver mit 100% CPU-Last läuft.

[mkinzler]

Lasse dir die Datenbank am besten in ein SQL-Skript auslagern (inklusive Insert-Statements). Dieses Skript kannst du dann anpassen und dann eine neue DB anlegen.

[buyden]

Das ging schon mal. Die Tabellen sind jetzt alle InnoDB.

Allerdings kann ich keinerlei ForeignKeys anlegen.

beim Anlegen damit

SQL-Code:

ALTER TABLE `repdb`.`auftrag` ADD CONSTRAINT `FK_auftrag_1` FOREIGN KEY `FK_auftrag_1` (`Artikel`)
    REFERENCES `artikel` (`Artikelnummer`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

kommt immer das:
Can't create table '.\repdb\#sql-130_5f.frm' (errno: 150)

Ich hab schon auf der MySQL-Seite nachgeschaut und die dort angegebenen Lösungsvorschläge mit zwei Test-Tabellen probiert, aber immer mit dem selben Fehler.

[buyden]

kann man da im Server was falsch konfigurieren, dass die FKs nicht angelegt werden können? InnoDB ist logischer Weise aktiviert.