These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Having Issues getting Blueprint Material lists from the static data dump
Author
Previous Topic Next Topic
Shang Ghjuvan
Shang Ghjuvan Corporation
#1 - 2012-08-31 00:55:07 UTC
I Have built two mysql querys which will grab the bp materials from the static data dump:

SELECT invTypes.typeid AS typeid, invTypes.typeName AS name, (invTypeMaterials.quantity * r.quantity) AS quantity
FROM invTypes
LEFT JOIN invTypeMaterials ON invTypeMaterials.materialTypeID = invTypes.typeID
LEFT JOIN ramTypeRequirements AS r ON invTypeMaterials.TypeID = r.requiredTypeID
LEFT JOIN invBlueprintTypes AS bt ON r.typeID = bt.blueprintTypeID
WHERE r.activityID =1
AND bt.blueprintTypeID = [BP ID]

SELECT t.typeid AS typeid, t.typename as name, (r.quantity * r.damagePerJob) AS quantity
FROM ramTypeRequirements AS r
LEFT JOIN invTypes AS t ON r.requiredTypeID = t.typeID
LEFT JOIN invBlueprintTypes AS bt ON r.typeID = bt.blueprintTypeID
LEFT JOIN invGroups AS g ON t.groupID = g.groupID
WHERE r.activityID =1
AND bt.blueprintTypeID = [BP ID]

These work great with some blueprints (Like the 3564/'Limos' Citadel Cruise Launcher I Blueprint) but not others (Like the 32445/Citadel Cruise Launcher I Blueprint) What is wrong with my querys?
Lutz Major
Austriae Est Imperare Orbi Universo
#2 - 2012-09-01 10:34:18 UTC
Shang,

You cannot query the materials in this way:

First of all, the typeID from the invTypeMaterials connects to the "product" typeID and the typeID from the ramTypeRequirements is the typeID from the blueprint!

SELECT * FROM invTypeMaterials WHERE typeID = 32444 (Citadel Cruise Launcher I)
SELECT * FROM ramTypeRequirements WHERE typeID = 32445 (Citadel Cruise Launcher I Blueprint)


Afterwards you have to apply waste the different wastages (like material, skill and product waste) differently to the raw materials (invTypeMaterials) and the extra materials (ramTypeRequirements)! So you can't add the materials together!


If you want to use only ONE query you will have to join the invTypeMaterials.typeID with invBlueprintTypes.productTypeID and the ramTypeRequirements with invBlueprintTypes.blueprintTypeID ... AND you must use an OUTER JOIN (not a left join).


Last, but not least: " (invTypeMaterials.quantity * r.quantity) AS quantity", ist totally bogus: you multiply the quantities ;) you will have to add them :)