eve_base_materials.sql

WITH unified_materials AS(
-- Find the base materials (used to calculate the 'base price' for an item?)
SELECT typename, quantity FROM invtypematerials AS mats
INNER JOIN invtypes ON invtypes.typeid = mats.materialtypeid
WHERE mats.typeid = 11993

UNION

-- Find the complex materials (Never present in T1 stuff)
SELECT invtypes.typename,

ram.quantity * mats.quantity * -1 AS quantity 
-- ram quantity is usually 1 but there a few weird exceptions for items that can reprocess their 'extra materials' for some reason.
-- 'mats' points to a completely different set of data from the query above the union - this mats ends up being the materials required for the base item, if there is one
--   we multiply by -1 to effectively subtract away the base materials from the base item. Sometimes there's a handful of base minerals left over.

FROM invblueprinttypes AS bt -- Start by finding the blueprint for our product type id
INNER JOIN ramtyperequirements AS ram ON ram.typeid = bt.blueprinttypeid -- Join in the ram type requirements on the *blueprint* id so we can find the base item(s)
INNER JOIN invtypematerials AS mats ON mats.typeid = ram.requiredtypeid -- Join in the materials for the base item(s)
INNER JOIN invtypes ON mats.materialtypeid = invtypes.typeid
WHERE bt.producttypeid = 11993
AND ram.activityid = 1 -- 1 is construction
AND ram.recycle = 1 -- 1 means it is a consumed material, probably called this because it factors into reprocessing calcs
)

SELECT typename, SUM(quantity) AS quantity FROM unified_materials
WHERE quantity > 0
GROUP BY typename
ORDER BY typename