JOIN (SELECT DISTINCT [..] substitute
- by FRKT
Hello,
I'd like to find a substitute for using SELECT DISTINCT in a derived table. Let's say I have three tables:
CREATE TABLE `trades` (
`tradeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`employeeID` int(11) unsigned NOT NULL,
`corporationID` int(11) unsigned NOT NULL,
`profit` int(11) NOT NULL,
KEY `tradeID` (`tradeID`),
KEY `employeeID` (`employeeID`),
KEY `corporationID` (`corporationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `corporations` (
`corporationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`corporationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `employees` (
`employeeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`employeeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Let's say I'd like to find out how much profit a specific employee has generated. Simple:
SELECT SUM(profit) FROM trades JOIN employees ON trades.employeeID = employees.employeeID AND employees.employeeID = 1;
It gets trickier if I'd like to query how much revenue a specific corporation has, however. I cannot simply replicate the aforementioned query, because two or more employees from the same company might be involved in the same trade. This query should do the trick:
SELECT SUM(profit) FROM trades JOIN (SELECT DISTINCT tradeID FROM trades WHERE trades.corporationID = 1)
... unfortunately, DISTINCT JOINs seem crazy ineffective. Is there any alternative I can use to determine how much revenue a corporation has, taking into account that a corporation might be listed several times with the same tradeID?
