Welcome, I've found IMHO "anomaly" in MySQL. I call it "anomaly" because : - it isn't intuitive; - other databases got other result;
Example description: Tables in databases: Kontrahent, Lokal, Umowa, UmowaTemp (contractor, place, contract, contractTemp) UmowaTemp is temporary table including newest contracts for place. Database create script <sql> -- -- Table structure for table `Kontrahent` -- CREATE TABLE `Kontrahent` ( `idKontrahent` int(11) NOT NULL default '0', `nazwa` varchar(255) NOT NULL default '', PRIMARY KEY (`idKontrahent`) ) TYPE=MyISAM; -- -- Dumping data for table `Kontrahent` -- INSERT INTO `Kontrahent` VALUES (1, 'Kontrahent1'); INSERT INTO `Kontrahent` VALUES (2, 'Kontrahent2'); -- -------------------------------------------------------- -- -- Table structure for table `Lokal` -- CREATE TABLE `Lokal` ( `idLokal` int(11) NOT NULL default '0', `nazwa` varchar(255) NOT NULL default '0', PRIMARY KEY (`idLokal`) ) TYPE=MyISAM; -- -- Dumping data for table `Lokal` -- INSERT INTO `Lokal` VALUES (1, 'Lokal1'); INSERT INTO `Lokal` VALUES (2, 'Lokal2'); -- -------------------------------------------------------- -- -- Table structure for table `Umowa` -- CREATE TABLE `Umowa` ( `idUmowa` int(11) NOT NULL default '0', `idLokal` int(11) NOT NULL default '0', `idKontrahent` int(11) NOT NULL default '0', `dataOd` date NOT NULL default '0000-00-00', PRIMARY KEY (`idUmowa`) ) TYPE=MyISAM; -- -- Dumping data for table `Umowa` -- INSERT INTO `Umowa` VALUES (1, 1, 1, '2004-08-06'); INSERT INTO `Umowa` VALUES (2, 1, 2, '2004-10-10'); -- -------------------------------------------------------- -- -- Table structure for table `UmowaTemp` -- CREATE TABLE `UmowaTemp` ( `idLokal` int(11) NOT NULL default '0', `dataOd` date NOT NULL default '0000-00-00' ) TYPE=MyISAM; -- -- Dumping data for table `UmowaTemp` -- INSERT INTO `UmowaTemp` VALUES (1, '2004-10-10'); </sql> I'm trying get "all locals, with data about last(newest) contracts". Last contracts are in UmowaTemp. I'm trying this SQL <sql> SELECT * FROM Lokal LEFT JOIN (Umowa JOIN UmowaTemp ON Umowa.idLokal = UmowaTemp.idLokal AND Umowa.dataOd=UmowaTemp.dataOd JOIN Kontrahent ON Umowa.idKontrahent = Kontrahent.idKontrahent ) ON Lokal.idLokal=Umowa.idLokal; </sql> IMHO: There are no errors in sql. However second record from MySQL result got repeated data from first record. 1 Lokal1 2 1 2 2004-10-10 1 2004-10-10 2 Kontrahent2 2 Lokal2 2 1 2 2004-10-10 1 2004-10-10 NULL NULL Generally in results values repeate for all records in fields of joined tables except last joined table, where is NULL. $ mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) Same behaviour in : mysql Ver 12.22 Distrib 4.0.20, for pc-linux-gnu (i386) Same query in PostgreSQL return IMHO good value, NULLs in second record are everywhere except table Lokal . 1 Lokal1 2 1 2 2004-10-10 1 2004-10-10 2 Kontrahent2 2 Lokal2 NULL NULL NULL NULL NULL NULL NULL $ psql --version psql (PostgreSQL) 7.4.2 I searched similar BUG in google and BugDB on mysql.com Regards -- Michał 'pingu' Lachowicz <http://home.elka.pw.edu.pl/~mlachowi/> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
