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