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 |