Description:
We are using the DISTINCT keyword for compatability purposes. According to the manual, it was introduced in MySQL 4.0.17. However, it does not seems supported in 4.1.1
}}} -------------------------------------------------------------------------
}}} Behavior in Mysql 4.1.1
}}} -------------------------------------------------------------------------
mysql> (select Sites_Code as Value, Sites_Name as Name
-> from Sites)
-> UNION DISTINCT
-> (select Site_Code as Value, Site_Name as Name
-> from Site);
ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT
(select Site_Code as Value, Site_Name as Name
from Site)' at line 3
How to repeat:
}}} --------------------------------------------------------------------------
}}} To Re-Create
}}} --------------------------------------------------------------------------
CREATE TABLE `Sites` (
`Sites_ID` int(11) NOT NULL auto_increment,
`Sites_Code` varchar(50) NOT NULL default '',
`Sites_Name` varchar(255) default NULL,
PRIMARY KEY (`Sites_ID`),
UNIQUE KEY `Sites_Code` (`Sites_Code`)
) TYPE=InnoDB;
CREATE TABLE `Site` (
`Site_ID` int(11) NOT NULL auto_increment,
`Site_Sites_ID` int(11) NOT NULL default '0',
`Site_Code` varchar(50) NOT NULL default '',
`Site_Name` varchar(255) default NULL,
PRIMARY KEY (`Site_Sites_ID`,`Site_ID`),
UNIQUE KEY `Site_ID` (`Site_ID`),
UNIQUE KEY `Site_Code` (`Site_Sites_ID`,`Site_Code`),
CONSTRAINT `0_928` FOREIGN KEY (`Site_Sites_ID`) REFERENCES `Sites` (`Sites_ID`) ON DELETE CASCADE
) TYPE=InnoDB;
insert into Sites values ('', 'lerner', 'lerner hall');
insert into Sites values ('', 'INTL', 'International');
insert into Site values('2', '1', 'lerner_403', '403 Lerner Hall');
(select Sites_Code as Value, Sites_Name as Name from Sites)
UNION DISTINCT
(select Site_Code as Value, Site_Name as Name from Site);
Description: We are using the DISTINCT keyword for compatability purposes. According to the manual, it was introduced in MySQL 4.0.17. However, it does not seems supported in 4.1.1 }}} ------------------------------------------------------------------------- }}} Behavior in Mysql 4.1.1 }}} ------------------------------------------------------------------------- mysql> (select Sites_Code as Value, Sites_Name as Name -> from Sites) -> UNION DISTINCT -> (select Site_Code as Value, Site_Name as Name -> from Site); ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT (select Site_Code as Value, Site_Name as Name from Site)' at line 3 How to repeat: }}} -------------------------------------------------------------------------- }}} To Re-Create }}} -------------------------------------------------------------------------- CREATE TABLE `Sites` ( `Sites_ID` int(11) NOT NULL auto_increment, `Sites_Code` varchar(50) NOT NULL default '', `Sites_Name` varchar(255) default NULL, PRIMARY KEY (`Sites_ID`), UNIQUE KEY `Sites_Code` (`Sites_Code`) ) TYPE=InnoDB; CREATE TABLE `Site` ( `Site_ID` int(11) NOT NULL auto_increment, `Site_Sites_ID` int(11) NOT NULL default '0', `Site_Code` varchar(50) NOT NULL default '', `Site_Name` varchar(255) default NULL, PRIMARY KEY (`Site_Sites_ID`,`Site_ID`), UNIQUE KEY `Site_ID` (`Site_ID`), UNIQUE KEY `Site_Code` (`Site_Sites_ID`,`Site_Code`), CONSTRAINT `0_928` FOREIGN KEY (`Site_Sites_ID`) REFERENCES `Sites` (`Sites_ID`) ON DELETE CASCADE ) TYPE=InnoDB; insert into Sites values ('', 'lerner', 'lerner hall'); insert into Sites values ('', 'INTL', 'International'); insert into Site values('2', '1', 'lerner_403', '403 Lerner Hall'); (select Sites_Code as Value, Sites_Name as Name from Sites) UNION DISTINCT (select Site_Code as Value, Site_Name as Name from Site);