Eve SQL of note: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
No edit summary |
No edit summary |
||
Line 75: | Line 75: | ||
ORDER BY a633.valueInt | ORDER BY a633.valueInt | ||
LIMIT 0 , 30 | LIMIT 0 , 30 | ||
</pre> | |||
=T2 bill of materials= | |||
altered from http://wiki.eve-id.net/Bill_of_Materials | |||
<pre> | |||
SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob | |||
FROM TL2MaterialsForTypeWithActivity AS materials | |||
INNER JOIN invTypes AS typeReq | |||
ON materials.requiredtypeID = typeReq.typeID | |||
INNER JOIN invBlueprintTypes AS bluePrint | |||
ON materials.typeID = bluePrint.blueprintTypeID | |||
INNER JOIN eveGraphics AS graphics | |||
ON typeReq.graphicID = graphics.graphicID | |||
WHERE materials.typeID = 20187 AND materials.activityId = 1 | |||
ORDER BY typeReq.typeName | |||
</pre> | </pre> | ||
[[Category:eve-online]] | [[Category:eve-online]] |
Revision as of 16:39, 26 January 2009
show Power Grid and Cpu requirements for station bits:
SELECT t.typeName, a30.valueInt AS PG, a50.valueInt AS CPU FROM invTypes t LEFT JOIN dgmTypeAttributes a30 ON t.typeID = a30.typeID LEFT JOIN dgmTypeAttributes a50 ON t.typeID = a50.typeID INNER JOIN invGroups g ON t.groupID = g.groupID WHERE g.categoryID =23 AND a30.attributeID =30 AND a50.attributeID =50 AND t.published =1 ORDER BY a30.valueInt DESC LIMIT 0 , 30
taken / adjusted from http://wiki.eve-id.net/Category:Database_Example_Queries
show what you get if you refine an aferburner:
select t.typeName as 'Refine', components.quantity, components.requiredTypeID, r.typeName as 'Get' from invTypes t left join TL2MaterialsForTypeWithActivity components on t.typeID=components.typeID inner join invTypes r on r.typeID=components.requiredTypeID where t.typeID=439 and t.published=1 and r.published=1 order by components.requiredTypeID
All atributes for an item:
SELECT dgmAttributeTypes.attributeID AS attributeID, dgmAttributeTypes.attributeName, dgmAttributeTypes.displayName, dgmTypeAttributes.valueInt, dgmTypeAttributes.valueFloat FROM dgmAttributeTypes, dgmTypeAttributes WHERE dgmAttributeTypes.published =1 AND dgmTypeAttributes.typeID =23009 AND dgmTypeAttributes.attributeID = dgmAttributeTypes.attributeID ORDER BY dgmAttributeTypes.attributeCategory, dgmAttributeTypes.attributeId LIMIT 0 , 30
(published)
SELECT dgmAttributeTypes.attributeID AS attributeID, dgmAttributeTypes.attributeName, dgmAttributeTypes.displayName, dgmTypeAttributes.valueInt, dgmTypeAttributes.valueFloat FROM dgmAttributeTypes, dgmTypeAttributes WHERE dgmTypeAttributes.typeID =23009 AND dgmTypeAttributes.attributeID = dgmAttributeTypes.attributeID ORDER BY dgmAttributeTypes.attributeCategory, dgmAttributeTypes.attributeId LIMIT 0 , 30
(all)
Show variations:
SELECT theitem.typeID AS typeID, theitem.typeName AS Name, a633.valueInt AS MetaLevel FROM invMetaTypes INNER JOIN invTypes AS theitem ON invMetaTypes.typeID = theitem.typeID LEFT JOIN dgmTypeAttributes AS a633 ON a633.typeID = theitem.typeID WHERE invMetaTypes.parentTypeID =230 AND a633.attributeID =633 ORDER BY a633.valueInt LIMIT 0 , 30
T2 bill of materials
altered from http://wiki.eve-id.net/Bill_of_Materials
SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob FROM TL2MaterialsForTypeWithActivity AS materials INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID INNER JOIN invBlueprintTypes AS bluePrint ON materials.typeID = bluePrint.blueprintTypeID INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID WHERE materials.typeID = 20187 AND materials.activityId = 1 ORDER BY typeReq.typeName