open All Channels
seplocked EVE Technology Lab
blankseplocked SQL query assistance
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Fiddlestx
Caldari
CompleXion Industries
CompleXion Alliance
Posted - 2011.08.05 16:20:00 - [1]
 

Hello,

I'm having a bit of trouble with getting some information

I have downloaded and imported the eve database dump but I'm having trouble with the sql to extract the information

I want it in this form but I don't konw sql well enough to get it to work. Any advice?

typeID -- itemName -- itemGroup -- itemCategory -- techLevel -- bill of materials in columns




Steve Ronuken
Posted - 2011.08.05 17:09:00 - [2]
 

The main problem you'll have is getting the bill of materials in seperate columns. That's a pivot table, and I can't help you there. They're also a pain to handle when you've got optional ones.


However, here's some sql that'll give you a list of all the materials needed to create item 631 (a Scythe)


You're having to self join as the details about the materials are stored in the same table as the main item. in the sql below, the alias 'item' refers to the main item, where as the alias 'materials' refers to the details of the materials.


I'd suggest you have a couple of queries. One to get the basic details, then one to get the bill of materials. Simplifies things.

select item.typeID,
item.typeName,
item.groupID,
materials.typeName name,
quantity
from invTypes materials,
invTypes item,
invTypeMaterials
where
invTypeMaterials.materialTypeID=materials.typeID
and invTypeMaterials.TypeID=item.typeID
and item.typeid=631;

Fiddlestx
Caldari
CompleXion Industries
CompleXion Alliance
Posted - 2011.08.05 19:36:00 - [3]
 

What I'm trying to do is create a table that I can query from in a spreadsheet. Is there any way to limit the results to only tech 1 items?

Dadunur
Posted - 2011.08.05 22:10:00 - [4]
 

This is what I've been using:


SELECT t.typeID, t.typeName, g.groupName, c.categoryName, b.techLevel
, wasteFactor = b.wasteFactor/100.0
, b.productionTime, b.researchMaterialTime
, b.researchCopyTime, b.researchProductivityTime
, tritanium = ISNULL(mt.quantity,0)
, pyerite = ISNULL(mp.quantity,0)
, mexallon = ISNULL(mx.quantity,0)
, isogen = ISNULL(mi.quantity,0)
, nocxium = ISNULL(mn.quantity,0)
, zydrine = ISNULL(mz.quantity,0)
, megacyte = ISNULL(mg.quantity,0)
, t.portionSize, b.productivityModifier
FROM invTypes t
JOIN invGroups g on t.groupID = g.groupID
JOIN invCategories c ON c.categoryID = g.categoryID
JOIN invBlueprintTypes b on b.productTypeID = t.typeID
LEFT JOIN invTypeMaterials mt on mt.typeID = t.typeID and mt.materialTypeID = 34
LEFT JOIN invTypeMaterials mp on mp.typeID = t.typeID and mp.materialTypeID = 35
LEFT JOIN invTypeMaterials mx on mx.typeID = t.typeID and mx.materialTypeID = 36
LEFT JOIN invTypeMaterials mi on mi.typeID = t.typeID and mi.materialTypeID = 37
LEFT JOIN invTypeMaterials mn on mn.typeID = t.typeID and mn.materialTypeID = 38
LEFT JOIN invTypeMaterials mz on mz.typeID = t.typeID and mz.materialTypeID = 39
LEFT JOIN invTypeMaterials mg on mg.typeID = t.typeID and mg.materialTypeID = 40


And just because I wanted to work it out, I think this is the "pivot" way:


SELECT typeID, typeName, groupName, categoryName, techLevel, wasteFactor
, Tritanium, Pyerite, Mexallon, Isogen, Nocxium, Zydrine, Megacyte, [Rocket Fuel]
, portionSize, productivityModifier
FROM (
SELECT t.typeID, t.typeName, g.groupName, c.categoryName, b.techLevel
, wasteFactor = b.wasteFactor/100.0
, b.productionTime, b.researchMaterialTime
, b.researchCopyTime, b.researchProductivityTime
, mt.quantity, materialTypeName = tm.typeName
, t.portionSize, b.productivityModifier
FROM invTypes t
JOIN invGroups g on t.groupID = g.groupID
JOIN invCategories c ON c.categoryID = g.categoryID
JOIN invBlueprintTypes b on b.productTypeID = t.typeID
JOIN invTypeMaterials mt on mt.typeID = t.typeID
JOIN invTypes tm ON tm.typeID = mt.materialTypeID
) p
PIVOT (
SUM(quantity)
FOR materialTypeName IN
( Tritanium, Pyerite, Mexallon, Isogen, Nocxium, Zydrine, Megacyte, [Rocket Fuel] )
) AS pvt




 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only