Eve SQL of note: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
No edit summary |
No edit summary |
||
Line 58: | Line 58: | ||
</pre> | </pre> | ||
(all) | (all) | ||
Show variations: | |||
<pre> | |||
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 | |||
</pre> | |||
[[Category:eve-online]] | [[Category:eve-online]] |
Revision as of 00:19, 14 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