full-materials-including-built-from-raw.sql

WITH RECURSIVE materials_list as (
    SELECT
        1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials.quantity as produces_qty,
        materials.materialTypeID as material_id,
        materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM invTypeMaterials as materials
    INNER JOIN invTypes as material_names ON materials.materialTypeID=material_names.typeID 
    INNER JOIN invTypes as produces_names ON materials.typeID=produces_names.typeID 
    WHERE materials.typeID = 28844 /** Top level Item */
    /** Standard materials not requiring reaction or schema combination */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.materialTypeID as material_id,
        materials.quantity * materials_list.material_qty as material_qty,
        material_names.typeName as material_name
    FROM materials_list, invTypeMaterials as materials
    INNER JOIN invTypes as material_names ON materials.materialTypeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON materials.typeID=produces_names.typeID
    WHERE materials.typeID=materials_list.material_id
    /** Planetary Items */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.typeID as material_id,
        (materials_list.material_qty / planetary_schema.quantity) * materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM materials_list, planetSchematicsTypeMap as planetary_schema
    LEFT OUTER JOIN planetSchematicsTypeMap as materials ON planetary_schema.schematicID=materials.schematicID AND materials.isInput = 1
    INNER JOIN invTypes as material_names ON materials.typeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON planetary_schema.typeID=produces_names.typeID
    WHERE planetary_schema.typeID=materials_list.material_id AND planetary_schema.isInput = 0 AND produces_names.groupID IN (1034,1040,1041,1042,1032,1033,1035)
    /** Moon Goo */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.typeID as material_id,
        (materials_list.material_qty / reaction_schema.quantity) * materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM materials_list, invTypeReactions as reaction_schema
    LEFT OUTER JOIN invTypeReactions as materials ON reaction_schema.reactionTypeID=materials.reactionTypeID AND materials.`input` = 1
    INNER JOIN invTypes as material_names ON materials.typeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON reaction_schema.typeID=produces_names.typeID
    WHERE reaction_schema.typeID=materials_list.material_id AND reaction_schema.`input` = 0 AND produces_names.groupID IN (427,428,429)
)
SELECT
    n,
    produces_id,
    produces_name,
    produces_qty,
    material_id,
    material_qty,
    material_name
FROM materials_list;

raw-and-initially-required-materials-list.sql

WITH RECURSIVE materials_list as (
    SELECT
        1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials.quantity as produces_qty,
        materials.materialTypeID as material_id,
        materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM invTypeMaterials as materials
    INNER JOIN invTypes as material_names ON materials.materialTypeID=material_names.typeID 
    INNER JOIN invTypes as produces_names ON materials.typeID=produces_names.typeID 
    WHERE materials.typeID = 28844 /** Top level Item */
    /** Standard materials not requiring reaction or schema combination */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.materialTypeID as material_id,
        materials.quantity * materials_list.material_qty as material_qty,
        material_names.typeName as material_name
    FROM materials_list, invTypeMaterials as materials
    INNER JOIN invTypes as material_names ON materials.materialTypeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON materials.typeID=produces_names.typeID
    WHERE materials.typeID=materials_list.material_id
    /** Planetary Items */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.typeID as material_id,
        (materials_list.material_qty / planetary_schema.quantity) * materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM materials_list, planetSchematicsTypeMap as planetary_schema
    LEFT OUTER JOIN planetSchematicsTypeMap as materials ON planetary_schema.schematicID=materials.schematicID AND materials.isInput = 1
    INNER JOIN invTypes as material_names ON materials.typeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON planetary_schema.typeID=produces_names.typeID
    WHERE planetary_schema.typeID=materials_list.material_id AND planetary_schema.isInput = 0 AND produces_names.groupID IN (1034,1040,1041,1042,1032,1033,1035)
    /** Moon Goo */
    UNION ALL
    SELECT
        n + 1 as n,
        produces_names.typeID as produces_id,
        produces_names.typeName as produces_name,
        materials_list.material_qty as produces_qty,
        materials.typeID as material_id,
        (materials_list.material_qty / reaction_schema.quantity) * materials.quantity as material_qty,
        material_names.typeName as material_name
    FROM materials_list, invTypeReactions as reaction_schema
    LEFT OUTER JOIN invTypeReactions as materials ON reaction_schema.reactionTypeID=materials.reactionTypeID AND materials.`input` = 1
    INNER JOIN invTypes as material_names ON materials.typeID=material_names.typeID
    INNER JOIN invTypes as produces_names ON reaction_schema.typeID=produces_names.typeID
    WHERE reaction_schema.typeID=materials_list.material_id AND reaction_schema.`input` = 0 AND produces_names.groupID IN (427,428,429)
)
SELECT
    n,
    produces_id,
    produces_name,
    produces_qty,
    material_id,
    SUM(material_qty) as material_qty,
    material_name
FROM materials_list
 WHERE material_id NOT IN (SELECT produces_id FROM materials_list)
 GROUP BY material_id;