Dyego Souza Dantas Leal <[EMAIL PROTECTED]> wrote on 11/07/2005 12:57:13 PM:
> Hello guys.. > > > I'm trying to use MySQL 5.0.15 , but my applications not work > > The Select : > > select PEDCERT.id as idped,count(*),'CE' as cer > from asddb.PEDCERT, asddb.MOVIMENTO m > where m.dat >= '2005/01/01' and m.dat <= '2005/12/31' and PEDCERT.idmov > = m.ID > group by m.dat; > > WORKS PERFECT !!!! > > > but , if i add a left join like : > > > select pdomeupau.id, count(*), sum(v0.valor), > 'CERTIDÕES' as cert > from asddb.PEDCERT, asddb.MOVIMENTO m > left join asddb.ORCAMENTO v0 on (v0.idato = asddb.PEDCERT.id) and > (v0.tipato = 'CC') and v0.codcta = '02' > where m.dat >= '2005/01/01' and m.dat <= '2005/12/31' and > pdomeupau.idmov = m.ID > group by m.dat > > tehe Server says > Unknowmn column "PEDCERT.id' in 'on clause' > > This is a bug ? > > MySQl 5.0.15 on Linux box using innodb tables. > > ------------------------------------------------------------------------- > ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento > ------------------------------------------------------------------------- > E S C R I B A I N F O R M A T I C A > ------------------------------------------------------------------------- > The only stupid question is the unasked one (somewhere in Linux's HowTo) > Linux registred user : #230601 > -- ICQ : 1647350 > $ look into "my eyes" Phone : +55 041 2106-1212 > look: cannot open my eyes Fax : +55 041 296 -6640 > ------------------------------------------------------------------------- > Reply: [EMAIL PROTECTED] > Bug? no. Please read http://dev.mysql.com/doc/refman/5.0/en/join.html for a full explanation as to why this is not a bug. What it boils down to is an "order of operations" issue (remember your algebra?). Explicit joins have a higher precedent than commas when evaluating a sequence of tables. So when it comes time to evaluate your original FROM clause: from asddb.PEDCERT, asddb.MOVIMENTO m left join asddb.ORCAMENTO v0 on (v0.idato = asddb.PEDCERT.id) and (v0.tipato = 'CC') and v0.codcta = '02' It is evaluated in this order from asddb.PEDCERT, ( asddb.MOVIMENTO m left join asddb.ORCAMENTO v0 on (v0.idato = asddb.PEDCERT.id) and (v0.tipato = 'CC') and v0.codcta = '02' ) As you can tell by the new parentheses I added, the table PEDCERT is not participating in the query by the time you look for one of its columns in your ON clause. You have at least 3 options to make your query work: A) Change the order of your comma-separated tables so that ORCAMENTO is joining directly to PEDCERT FROM asddb.MOVIMENTO m, asddb.PEDCERT LEFT JOIN ... B) Add parentheses to your original statement to join PEDCERT to MOVIMENTO *before* joining to ORCAMENTO FROM (asddb.PEDCERT, asddb.MOVIMENTO m) LEFT JOIN ... C) Only use explicit JOIN statements: FROM asddb.PEDCERT CROSS JOIN adddb.MOVIMENTO m LEFT JOIN asddb.ORCAMENTO v0 on v0.idato = asddb.PEDCERT.id and v0.tipato = 'CC' and v0.codcta = '02' ... Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
