Eve SQL of note

Attributes

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

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)

Variations

show variations and meta levels:

• no meta zero included.
• need to know the meta zero item before hand.
• meta zero and parent are the same thing.

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 

a variation on showing variations:

• faster
• no attributes
• need to know the meta zero item before.

SELECT
 invMetaTypes.typeID,
 invMetaTypes.parentTypeID,
 invMetaTypes.metaGroupID,
 invTypes.typeName
FROM
 `invMetaTypes`
 LEFT JOIN invTypes ON invMetaTypes.typeID = invTypes.typeID
WHERE `parentTypeID` =568

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.activity = 1
ORDER BY typeReq.typeName

material refine

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

as of dominion: /Denormalizing invTypeMaterials and ramTypeRequirements