Outer Koin liefert nicht die erwarteten Ergebnisse - Bug?

[Andre Krajnik]

Hi zusammen.

Mein Problem besteht auf 2 Servern (Versionen s.u.).

Ich brauche alle Datensätze pro Tag, die in der Table ScriptConfig einen 
Eintag haben, aber in der Tabelle Abrechnungsdaten nicht vorhanden sind.

Dieses Problem löse ich mittels einem left outer join:

Select CURDATE(), sc.Kunde, sc.SysID, sc.Grund, sc.Wert, a.Wert as 
Abrechnung, a.SysID, a.Grund, a.Kunde, a.Datum
from ScriptConfig sc left outer join Abrechnungsdaten a
on sc.Kunde=a.Kunde and sc.Grund=a.Grund and sc.SysID=a.SysID and 
a.Datum=CURDATE()
order by sc.Kunde

Am Beispiel von heute bekomme ich 2 Datensätze zuviel:

2002-05-07,bre,s0031652,Notes-Hosting,e:\Lotus\Domino\Data\dbs\bre,NULL,NULL,NULL,NULL,NULL
2002-05-07,db-ras,s0031622,Database,dbras,NULL,NULL,NULL,NULL,NULL
2002-05-07,db-ras,s0031622,Webspace,/data/html/dbras,NULL,NULL,NULL,NULL,NULL
2002-05-07,dbpremierone,s0031406,Mail,_(at)_maxblue.co.uk,NULL,NULL,NULL,NULL,NULL
2002-05-07,dbpremierone,s0031406,Mail,_(at)_dbpremiereone.com,NULL,NULL,NULL,NULL,NULL
2002-05-07,dbu,s0031622,Webspace,/data/html/dbu,32768,s0031622,Webspace,dbu,2002-05-07
2002-05-07,dbu,s0031622,Database,dbu,4096,s0031622,Database,dbu,2002-05-07

nämlich die beiden letzten!!!!!



Hier folgen noch die Table-Definitions und Muster-Daten.

Wer von den MySQL-Gurus klärt mich auf wo mein (Denk-?)Fehler liegt. 
Oder habe ich 'nen Bug entdeckt??






# MySQL-Front Dump 2.0
#
# Host: 10.10.10.10   Database: Abrechnung
#--------------------------------------------------------
# Server version 3.23.23-beta-log      => Mandrake 7.2
# Server version 3.23.41               => Redhat 7.2


#
# Table structure for table 'Abrechnungsdaten'
#

create table Abrechnungsdaten(Datum date not null default 
'0000-00-00',Kunde char(15) not null default '*',SysID char(8) not null 
default '        

',Grund 
enum('Webspace','Database','Transfer','ftp','Notes-Hosting','Mail') not 
null default 'Webspace',FlussRichtung enum('In','Out') not null default 
'In',Anzahl bigint(20) unsigned default NULL,Wert bigint(20) default 
NULL,primary key (Datum,Kunde,Grund,FlussRichtung,SysID)) type=MyISAM;



#
# Dumping data for table 'Abrechnungsdaten'
#

INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","vc-core","s0031054","Webspace","In","","8990720");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","vc-core","s0031054","Database","In","","61440");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","vc-core","s0031054","Transfer","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","antivir","","Webspace","In","","528674816");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","antivir","","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","antivir","","Transfer","In","","118312");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","db-ras","s0031054","Webspace","In","","8835072");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","db-ras","s0031054","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","db-ras","s0031054","Transfer","In","","54727");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","sinius","s0031173","Webspace","In","","533991424");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","sinius","s0031173","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","sinius","s0031173","Transfer","In","","6746546");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","hotline","s0031173","Webspace","In","","85381120");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","hotline","s0031173","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","hotline","s0031173","Transfer","In","","165");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbras","","Webspace","In","","20385792");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbras","","Database","In","","139264");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbras","","Transfer","In","","53079");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbu","s0031622","Webspace","In","","32768");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbu","s0031622","Database","In","","4096");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-06","dbu","s0031622","Transfer","In","","17121");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","vc-core","s0031054","Webspace","In","","8990720");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","vc-core","s0031054","Database","In","","61440");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","vc-core","s0031054","Transfer","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","antivir","","Webspace","In","","528674816");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","antivir","","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","antivir","","Transfer","In","","118312");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","db-ras","s0031054","Webspace","In","","8835072");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","db-ras","s0031054","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","db-ras","s0031054","Transfer","In","","16107963");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","sinius","s0031173","Webspace","In","","534257664");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","sinius","s0031173","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","sinius","s0031173","Transfer","In","","169052461");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","hotline","s0031173","Webspace","In","","85381120");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","hotline","s0031173","Database","In","","0");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","hotline","s0031173","Transfer","In","","165");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","sinius","s0031173","ftp","In","","10549091");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbras","","Webspace","In","","20385792");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbras","","Database","In","","139264");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbras","","Transfer","In","","53079");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbu","s0031622","Webspace","In","","32768");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbu","s0031622","Database","In","","4096");
INSERT INTO `Abrechnungsdaten` 
VALUES("2002-05-07","dbu","s0031622","Transfer","In","","17121");


#
# Table structure for table 'ScriptConfig'
#

create table ScriptConfig(Kunde varchar(15) not null default ' 
              
                                                  ',SysID 
varchar(8) not null default '        
 
              ',Grund 
enum('Webspace','Database','Transfer','ftp','Notes-Hosting','Mail') not 
null default 'Webspace',Wert varchar(50) default NULL) type=MyISAM;



#
# Dumping data for table 'ScriptConfig'
#
INSERT INTO `ScriptConfig` 
VALUES("dbpremierone","s0031406","Mail","_(at)_samplemail.com");
INSERT INTO `ScriptConfig` 
VALUES("dbpremierone","s0031406","Mail","_(at)_samplemail.co.uk");
INSERT INTO `ScriptConfig` 
VALUES("bre","s0031652","Notes-Hosting","e:\\Lotus\\Domino\\Data\\dbs\\bre");
INSERT INTO `ScriptConfig` 
VALUES("dbu","s0031622","Webspace","/data/html/dbu");
INSERT INTO `ScriptConfig` VALUES("dbu","s0031622","Database","dbu");
INSERT INTO `ScriptConfig` 
VALUES("db-ras","s0031622","Webspace","/data/html/dbras");
INSERT INTO `ScriptConfig` VALUES("db-ras","s0031622","Database","dbras");








-- 
mfg

Andre

---
Infos zur Mailingliste, zur Teilnahme und zum An- und Abmelden unter
-->>  http://www.4t2.com/mysql