Re: UNION Queries

[freddie]

Here we go. I hope this contains all the information you need. thanks.

Brokers Table
DROP TABLE IF EXISTS `stocklist`.`brokers`;
CREATE TABLE `brokers` (
`BrokerID` int(10) NOT NULL auto_increment,
`Broker` varchar(50) default NULL,
PRIMARY KEY (`BrokerID`),
KEY `BrokerID` (`BrokerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Data:
1, 'CANACCORD CAPITAL'
2, 'CLSA'
3, 'DESJARDINS SECURITIES'
4, 'DEUTSCHE BANK'
5, 'HARGREAVE HALE'
6, 'HAYWOOD SECURITIES INC'
7, 'ING'

Currencies Table:
DROP TABLE IF EXISTS `stocklist`.`countrylist`;
CREATE TABLE `countrylist` (
`CountryID` varchar(50) NOT NULL default '',
`RegionID` varchar(50) default NULL,
`CountryName` varchar(50) default NULL,
PRIMARY KEY (`CountryID`),
KEY `IndustryID` (`CountryID`),
KEY `RegionID` (`RegionID`),
KEY `RegionsCountryList` (`RegionID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Data:
'AO', 'Africa', 'Angola'
'AU', 'Asia Ex Japan', 'Australia'
'BR', 'South America', 'Brazil'
'CA', 'North America', 'Canada'
'CD', 'Africa', 'Democratic Republic Of Congo'
'CN', 'Asia Ex Japan', 'China'
'DZ', 'Africa', 'Algeria'
'FK', 'Europe', 'Falklands'
'GB', 'Europe', 'United Kingdom'
'GR', 'Europe', 'Greenland'
'HK', 'Asia Ex Japan', 'Hong Kong'
'ID', 'Asia Ex Japan', 'Indonesia'
'IE', 'Europe', 'Ireland'
'IN', 'Asia Ex Japan', 'India'
'JP', 'Japan', 'Japan'


This SQL returns the error:
SELECT countrylist.`countryid`, countrylist.`regionid`, countrylist.`countryname` FROM `countrylist`
UNION
SELECT brokers.`brokerID`, brokers.`broker`, "test" as Test FROM `brokers`