LifeHatesMe
   |
Posted - 2011.01.10 12:33:00 -
[1]
I was messing around, and wrote this code;SELECT `ship`.`typeName`, `ship`.`typeID`, `ship`.`groupID`, `ship`.`marketGroupID`
FROM `invTypes` AS `ship`
WHERE (`ship`.`groupID` = 26
OR `ship`.`groupID` = 419)
AND `ship`.`marketGroupID` IS NOT NULL
ORDER BY `ship`.`marketGroupID` Shows a list of every tech 1 cruiser, and battlecruiser in game.
So then I took it a little bit farther.. and wrote this:
SET SESSION SQL_BIG_SELECTS=1;
SELECT DISTINCT `ship`.`typeName` AS `Ship`,
`ship`.`marketGroupID` AS `marketGroupID`,
`ship`.`groupID` AS `groupID`,
`ship`.`typeID` AS `typeID`,
`tri`.`quantity` AS `Tritanium`,
`pye`.`quantity` AS `Pyerite`,
`mex`.`quantity` AS `Mexallon`,
`iso`.`quantity` AS `Isogen`,
`noc`.`quantity` AS `Nocxium`,
`zyd`.`quantity` AS `Zydrine`,
`meg`.`quantity` AS `Megacyte`,
(`tri`.`quantity` * 2.15 +
`pye`.`quantity` * 4.00 +
`mex`.`quantity` * 28.30 +
`iso`.`quantity` * 60.00 +
`noc`.`quantity` * 428.01 +
`zyd`.`quantity` * 1079.99 +
`meg`.`quantity` * 2891.73) AS `Cost`
FROM `invTypes` AS `ship`
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `tri`
ON `ship`.`typeID` = `tri`.`typeID`
AND `tri`.`requiredTypeID` = 34
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `pye`
ON `ship`.`typeID` = `pye`.`typeID`
AND `pye`.`requiredTypeID` = 35
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `mex`
ON `ship`.`typeID` = `mex`.`typeID`
AND `mex`.`requiredTypeID` = 36
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `iso`
ON `ship`.`typeID` = `iso`.`typeID`
AND `iso`.`requiredTypeID` = 37
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `noc`
ON `ship`.`typeID` = `noc`.`typeID`
AND `noc`.`requiredTypeID` = 38
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `zyd`
ON `ship`.`typeID` = `zyd`.`typeID`
AND `zyd`.`requiredTypeID` = 39
INNER JOIN `TL2MaterialsForTypeWithActivity` AS `meg`
ON `ship`.`typeID` = `meg`.`typeID`
AND `meg`.`requiredTypeID` = 40
WHERE (`ship`.`GroupID` = 26
OR `ship`.`GroupID` = 419)
AND `ship`.`marketGroupID` IS NOT NULL
ORDER BY `ship`.`marketGroupID` It works perfectly, but.. ! For some very odd reason, it shows only the Drake, and the Ferox battlecruiser, but nothing else. The cruisers are listed perfectly. Also, the query is slow.. about 1.5 seconds. What am I doing wrong here?
MySQL Version: 5.1.47
______________________________
War... War never ends. |