Eve SQL of note: Difference between revisions

From Federal Burro of Information
Jump to navigationJump to search
No edit summary
No edit summary
Line 35: Line 35:
order by components.requiredTypeID
order by components.requiredTypeID
</pre>
</pre>
All atributes for an item:
<pre>
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
</pre>
(published)
<pre>
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
</pre>
(all)


[[Category:eve-online]]
[[Category:eve-online]]

Revision as of 00:17, 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)