Dadunur
|
Posted - 2011.08.05 22:10:00 -
[4]
This is what I've been using:
SELECT t.typeID, t.typeName, g.groupName, c.categoryName, b.techLevel , wasteFactor = b.wasteFactor/100.0 , b.productionTime, b.researchMaterialTime , b.researchCopyTime, b.researchProductivityTime , tritanium = ISNULL(mt.quantity,0) , pyerite = ISNULL(mp.quantity,0) , mexallon = ISNULL(mx.quantity,0) , isogen = ISNULL(mi.quantity,0) , nocxium = ISNULL(mn.quantity,0) , zydrine = ISNULL(mz.quantity,0) , megacyte = ISNULL(mg.quantity,0) , t.portionSize, b.productivityModifier FROM invTypes t JOIN invGroups g on t.groupID = g.groupID JOIN invCategories c ON c.categoryID = g.categoryID JOIN invBlueprintTypes b on b.productTypeID = t.typeID LEFT JOIN invTypeMaterials mt on mt.typeID = t.typeID and mt.materialTypeID = 34 LEFT JOIN invTypeMaterials mp on mp.typeID = t.typeID and mp.materialTypeID = 35 LEFT JOIN invTypeMaterials mx on mx.typeID = t.typeID and mx.materialTypeID = 36 LEFT JOIN invTypeMaterials mi on mi.typeID = t.typeID and mi.materialTypeID = 37 LEFT JOIN invTypeMaterials mn on mn.typeID = t.typeID and mn.materialTypeID = 38 LEFT JOIN invTypeMaterials mz on mz.typeID = t.typeID and mz.materialTypeID = 39 LEFT JOIN invTypeMaterials mg on mg.typeID = t.typeID and mg.materialTypeID = 40
And just because I wanted to work it out, I think this is the "pivot" way:
SELECT typeID, typeName, groupName, categoryName, techLevel, wasteFactor , Tritanium, Pyerite, Mexallon, Isogen, Nocxium, Zydrine, Megacyte, [Rocket Fuel] , portionSize, productivityModifier FROM ( SELECT t.typeID, t.typeName, g.groupName, c.categoryName, b.techLevel , wasteFactor = b.wasteFactor/100.0 , b.productionTime, b.researchMaterialTime , b.researchCopyTime, b.researchProductivityTime , mt.quantity, materialTypeName = tm.typeName , t.portionSize, b.productivityModifier FROM invTypes t JOIN invGroups g on t.groupID = g.groupID JOIN invCategories c ON c.categoryID = g.categoryID JOIN invBlueprintTypes b on b.productTypeID = t.typeID JOIN invTypeMaterials mt on mt.typeID = t.typeID JOIN invTypes tm ON tm.typeID = mt.materialTypeID ) p PIVOT ( SUM(quantity) FOR materialTypeName IN ( Tritanium, Pyerite, Mexallon, Isogen, Nocxium, Zydrine, Megacyte, [Rocket Fuel] ) ) AS pvt
|