The relationship looks righteous enough but I note that you use 'straight join' in your expression, rather than 'straight_join' as indicated in the manual (http://dev.mysql.com/doc/refman/5.1/en/join.html).
Perhaps the message is a red herring and your trouble is elsewhere? - michael dykman On Wed, Aug 11, 2010 at 4:25 PM, Mike Spreitzer <mspre...@us.ibm.com> wrote: > Why is it that a field name that works fine for a JOIN is invalid in a > STRAIGHT JOIN? > > mysql> show create table fldsndm; > +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | Table | Create Table | > +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | fldsndm | CREATE TABLE `fldsndm` ( > `p` varchar(200) NOT NULL, > `cd` datetime NOT NULL, > `cms` smallint(6) NOT NULL, > `pip` char(15) NOT NULL, > `pport` smallint(6) NOT NULL, > `pboot` bigint(20) NOT NULL, > `msgid` bigint(20) NOT NULL, > `startgtime` bigint(20) NOT NULL, > `datalen` int(11) NOT NULL, > `toself` tinyint(1) DEFAULT NULL, > `sepoch` bigint(20) NOT NULL DEFAULT '0', > `c` decimal(11,3) NOT NULL DEFAULT '0.000', > UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`) > ) ENGINE=MEMORY DEFAULT CHARSET=latin1 | > +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> show create table fldrcv; > +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | Table | Create Table | > +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | fldrcv | CREATE TABLE `fldrcv` ( > `p` varchar(200) NOT NULL, > `cd` datetime NOT NULL, > `cms` smallint(6) NOT NULL, > `pip` char(15) NOT NULL, > `pport` smallint(6) NOT NULL, > `pboot` bigint(20) DEFAULT NULL, > `qip` char(15) NOT NULL, > `qport` smallint(6) NOT NULL, > `qboot` bigint(20) DEFAULT NULL, > `msgid` bigint(20) NOT NULL, > `startgtime` bigint(20) NOT NULL, > `datalen` int(11) NOT NULL, > `q` varchar(200) DEFAULT NULL, > `repoch` bigint(20) NOT NULL DEFAULT '0', > `c` decimal(11,3) NOT NULL DEFAULT '0.000', > KEY `c` (`c`), > KEY `pec` (`p`,`repoch`,`c`), > KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`), > KEY `qbm` (`q`,`qboot`,`msgid`), > KEY `pbm` (`p`,`pboot`,`msgid`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> explain extended select * from fldrcv join fldsndm on > (fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and > fldrcv.msgid=fldsndm.msgid); > +----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+----------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | filtered | Extra | > +----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+----------+-------------+ > | 1 | SIMPLE | fldsndm | ALL | pbm | NULL | NULL | > NULL | 29036 | 100.00 | | > | 1 | SIMPLE | fldrcv | ref | qbm | qbm | 220 | > bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot,bigCell2906_flood.fldsndm.msgid > | 452 | 100.00 | Using where | > +----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+----------+-------------+ > 2 rows in set, 1 warning (0.00 sec) > > mysql> explain extended select * from fldrcv straight join fldsndm on > (fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and > fldrcv.msgid=fldsndm.msgid); > ERROR 1054 (42S22): Unknown column 'fldrcv.q' in 'on clause' > mysql> > > Thanks, > Mike Spreitzer > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
