rathena useful queries

item_category_query.sql

WITH monster_drops
AS
(
SELECT id as monster_id, MVP1id AS item_id, MVP1per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, MVP2id AS item_id, MVP2per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, MVP3id AS item_id, MVP3per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop1id AS item_id, Drop1per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop2id AS item_id, Drop2per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop3id AS item_id, Drop3per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop4id AS item_id, Drop4per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop5id AS item_id, Drop5per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop6id AS item_id, Drop6per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop7id AS item_id, Drop7per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop8id AS item_id, Drop8per AS item_rate FROM mob_db
UNION
SELECT id as monster_id, Drop9id AS item_id, Drop9per AS item_rate FROM mob_db
), combined_with_items AS (
SELECT 
d.monster_id, d.item_id, d.item_rate,
i.name_japanese as name,
/*i.weight,*/
/*i.attack, i.defence,*/ 
i.`range`, i.slots,
-- i.script,
/*i.equip_locations, i.equip_jobs,*/
i.equip_level, i.weapon_level, 
i.refineable,
i.view,
    CASE
        WHEN i.type = 3 THEN ''
        WHEN i.type = 0 THEN 'Useable_Healing'
        WHEN i.type = 2 THEN 'Usable_Other'
        WHEN i.type = 3 THEN 'Misc'
        WHEN i.type = 4 THEN 'Armor'
        WHEN i.type = 5 THEN 'Weapon'
        WHEN i.type = 6 THEN 'Card'
        WHEN i.type = 7 THEN 'Pet_Egg'
        WHEN i.type = 8 THEN 'Pet_Equipment'
        WHEN i.type = 10 THEN 'Arrow/Ammunition'
        WHEN i.type = 11 THEN 'Delay_Consume'
        WHEN i.type = 12 THEN 'Shadow_Equipment'
        WHEN i.type = 18 THEN 'Delay_Consume2'
        ELSE 'Unknown'
    END AS item_type,
    

/*CASE WHEN i.type = 5 AND i.view = 0 THEN 1 ELSE 0 END AS BareFist,*/
CASE WHEN i.type = 5 AND i.view = 1 THEN 1 ELSE 0 END AS Daggers,
CASE WHEN i.type = 5 AND i.view = 2 THEN 1 ELSE 0 END AS OneHandedSwords,
CASE WHEN i.type = 5 AND i.view = 3 THEN 1 ELSE 0 END AS TwoHandedSwords,
CASE WHEN i.type = 5 AND i.view = 4 THEN 1 ELSE 0 END AS OneHandedSpears,
CASE WHEN i.type = 5 AND i.view = 5 THEN 1 ELSE 0 END AS TwoHandedSpears,
CASE WHEN i.type = 5 AND i.view = 6 THEN 1 ELSE 0 END AS OneHandedAxes,
CASE WHEN i.type = 5 AND i.view = 7 THEN 1 ELSE 0 END AS TwoHandedAxes,
CASE WHEN i.type = 5 AND i.view = 8 THEN 1 ELSE 0 END AS Maces,
/*CASE WHEN i.type = 5 AND i.view = 9 THEN 1 ELSE 0 END AS Unused,*/
CASE WHEN i.type = 5 AND i.view = 10 THEN 1 ELSE 0 END AS Staves,
CASE WHEN i.type = 5 AND i.view = 11 THEN 1 ELSE 0 END AS Bows,
CASE WHEN i.type = 5 AND i.view = 12 THEN 1 ELSE 0 END AS Knuckles,
CASE WHEN i.type = 5 AND i.view = 13 THEN 1 ELSE 0 END AS MusicalInstruments,
CASE WHEN i.type = 5 AND i.view = 14 THEN 1 ELSE 0 END AS Whips,
CASE WHEN i.type = 5 AND i.view = 15 THEN 1 ELSE 0 END AS Books,
CASE WHEN i.type = 5 AND i.view = 16 THEN 1 ELSE 0 END AS Katars,
CASE WHEN i.type = 5 AND i.view = 17 THEN 1 ELSE 0 END AS Revolvers,
CASE WHEN i.type = 5 AND i.view = 18 THEN 1 ELSE 0 END AS Rifles,
CASE WHEN i.type = 5 AND i.view = 19 THEN 1 ELSE 0 END AS GatlingGuns,
CASE WHEN i.type = 5 AND i.view = 20 THEN 1 ELSE 0 END AS Shotguns,
CASE WHEN i.type = 5 AND i.view = 21 THEN 1 ELSE 0 END AS GrenadeLaunchers,
CASE WHEN i.type = 5 AND i.view = 22 THEN 1 ELSE 0 END AS FuumaShurikens,
CASE WHEN i.type = 5 AND i.view = 23 THEN 1 ELSE 0 END AS TwoHandedStaves,
/*
CASE WHEN i.type = 5 AND i.view = 24 THEN 1 ELSE 0 END AS MaxType,
CASE WHEN i.type = 5 AND i.view = 25 THEN 1 ELSE 0 END AS DualwieldDaggers,
CASE WHEN i.type = 5 AND i.view = 26 THEN 1 ELSE 0 END AS DualwieldSwords,
CASE WHEN i.type = 5 AND i.view = 27 THEN 1 ELSE 0 END AS DualwieldAxes,
CASE WHEN i.type = 5 AND i.view = 28 THEN 1 ELSE 0 END AS DaggerSword,
CASE WHEN i.type = 5 AND i.view = 29 THEN 1 ELSE 0 END AS DaggerAxe,
CASE WHEN i.type = 5 AND i.view = 30 THEN 1 ELSE 0 END AS SwordAxe,
    */
    CASE WHEN i.type = 4 AND i.view >= 1 AND i.view <= 4 THEN 1 ELSE 0 END AS Shield,
    CASE WHEN i.type = 4 AND i.view = 1 THEN 1 ELSE 0 END AS Shield_Guard,
    CASE WHEN i.type = 4 AND i.view = 2 THEN 1 ELSE 0 END AS Shield_Buckler,
    CASE WHEN i.type = 4 AND i.view = 3 THEN 1 ELSE 0 END AS Shield_Shield,
    CASE WHEN i.type = 4 AND i.view = 4 THEN 1 ELSE 0 END AS Shield_LargeShield,
    /*CASE WHEN i.equip_locations IS NULL THEN 0 ELSE 1 END AS isEquip,*/
    CASE WHEN i.equip_locations & 1 THEN 1 ELSE 0 END AS LowerHeadgear,
    CASE WHEN i.equip_locations & 2 THEN 1 ELSE 0 END AS RightHand,
    CASE WHEN i.equip_locations & 4 THEN 1 ELSE 0 END AS Garment,
    CASE WHEN i.equip_locations & 8 THEN 1 ELSE 0 END AS Accessory1,
    CASE WHEN i.equip_locations & 16 THEN 1 ELSE 0 END AS Armor,
    CASE WHEN i.equip_locations & 32 THEN 1 ELSE 0 END AS Lefthand,
    CASE WHEN i.equip_locations & 64 THEN 1 ELSE 0 END AS Shoes,
    CASE WHEN i.equip_locations & 128 THEN 1 ELSE 0 END AS Accessory2,
    CASE WHEN i.equip_locations & 256 THEN 1 ELSE 0 END AS Upperheadgear,
    CASE WHEN i.equip_locations & 512 THEN 1 ELSE 0 END AS Middleheadgear,
    /*
    CASE WHEN i.equip_locations & 1024 THEN 1 ELSE 0 END AS CostumeUpperheadgear,
    CASE WHEN i.equip_locations & 2048 THEN 1 ELSE 0 END AS CostumeMiddleheadgear,
    CASE WHEN i.equip_locations & 4096 THEN 1 ELSE 0 END AS CostumeLowerheadgear,
    CASE WHEN i.equip_locations & 8192 THEN 1 ELSE 0 END AS CostumeGarment,
    CASE WHEN i.equip_locations & 32768 THEN 1 ELSE 0 END AS Arrow_Ammo,
    CASE WHEN i.equip_locations & 65536 THEN 1 ELSE 0 END AS ShadowArmor,
    CASE WHEN i.equip_locations & 131072 THEN 1 ELSE 0 END AS ShadowWeapon,
    CASE WHEN i.equip_locations & 262144 THEN 1 ELSE 0 END AS ShadowShield,
    CASE WHEN i.equip_locations & 524288 THEN 1 ELSE 0 END AS ShadowShoes,
    CASE WHEN i.equip_locations & 1048576 THEN 1 ELSE 0 END AS ShadowAccessory2,
    CASE WHEN i.equip_locations & 2097152 THEN 1 ELSE 0 END AS ShadowAccessory1,
    */
    CASE WHEN i.equip_locations & 32 AND i.equip_locations & 2 THEN 1 ELSE 0 END AS TwoHanded,
    CASE WHEN i.equip_locations & 32 AND i.equip_locations & 2 AND i.range>= 5 THEN 1 ELSE 0 END AS Ranged,
    
CASE WHEN i.equip_jobs & 1 THEN 1 ELSE 0 END AS Novice,
CASE WHEN i.equip_jobs & 2 THEN 1 ELSE 0 END AS Swordman,
CASE WHEN i.equip_jobs & 4 THEN 1 ELSE 0 END AS Magician,
CASE WHEN i.equip_jobs & 8 THEN 1 ELSE 0 END AS Archer,
CASE WHEN i.equip_jobs & 16 THEN 1 ELSE 0 END AS Acolyte,
CASE WHEN i.equip_jobs & 32 THEN 1 ELSE 0 END AS Merchant,
CASE WHEN i.equip_jobs & 64 THEN 1 ELSE 0 END AS Thief,
CASE WHEN i.equip_jobs & 128 THEN 1 ELSE 0 END AS Knight,
CASE WHEN i.equip_jobs & 256 THEN 1 ELSE 0 END AS Priest,
CASE WHEN i.equip_jobs & 512 THEN 1 ELSE 0 END AS Wizard,
CASE WHEN i.equip_jobs & 1024 THEN 1 ELSE 0 END AS Blacksmith,
CASE WHEN i.equip_jobs & 2048 THEN 1 ELSE 0 END AS Hunter,
CASE WHEN i.equip_jobs & 4096 THEN 1 ELSE 0 END AS Assassin,
/*CASE WHEN i.equip_jobs & 8192 THEN 1 ELSE 0 END AS Unused2,*/
CASE WHEN i.equip_jobs & 16384 THEN 1 ELSE 0 END AS Crusader,
CASE WHEN i.equip_jobs & 32768 THEN 1 ELSE 0 END AS Monk,
CASE WHEN i.equip_jobs & 65536 THEN 1 ELSE 0 END AS Sage,
CASE WHEN i.equip_jobs & 131072 THEN 1 ELSE 0 END AS Rogue,
CASE WHEN i.equip_jobs & 262144 THEN 1 ELSE 0 END AS Alchemist,
CASE WHEN i.equip_jobs & 524288 THEN 1 ELSE 0 END AS Bard_Dancer,
/*CASE WHEN i.equip_jobs & 1048576 THEN 1 ELSE 0 END AS Unused,*/
CASE WHEN i.equip_jobs & 2097152 THEN 1 ELSE 0 END AS Taekwon,
CASE WHEN i.equip_jobs & 4194304 THEN 1 ELSE 0 END AS StarGladiator,
CASE WHEN i.equip_jobs & 8388608 THEN 1 ELSE 0 END AS SoulLinker,
CASE WHEN i.equip_jobs & 16777216 THEN 1 ELSE 0 END AS Gunslinger,
CASE WHEN i.equip_jobs & 33554432 THEN 1 ELSE 0 END AS Ninja
/*CASE WHEN i.equip_jobs & 67108864 THEN 1 ELSE 0 END AS Gangsi,
CASE WHEN i.equip_jobs & 134217728 THEN 1 ELSE 0 END AS DeathKnight,
CASE WHEN i.equip_jobs & 268435456 THEN 1 ELSE 0 END AS DarkCollector,
CASE WHEN i.equip_jobs & 536870912 THEN 1 ELSE 0 END AS Kagerou_Oboro,
CASE WHEN i.equip_jobs & 1073741824 THEN 1 ELSE 0 END AS Rebellion,
CASE WHEN i.equip_jobs & 2147483648 THEN 1 ELSE 0 END AS Summoner*/
    
FROM monster_drops d
JOIN item_db i ON i.id = d.item_id
WHERE i.type IN (4,5)
)
SELECT *
FROM combined_with_items
ORDER BY monster_id, item_id