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