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 <mspreitz@stripped> 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
- mdykman@stripped
May the Source be with you.
