Merchants with Item:
select npc_types.name, spawn2.zone from merchantlist
inner join npc_types on npc_types.merchant_id = merchantlist.merchantid
inner join spawnentry on spawnentry.npcID = npc_types.id
inner join spawn2 on spawn2.spawngroupID = spawnentry.spawngroupID
where item = INSERT ITEM ID;
Mobs that drop an Item:
select npc_types.name, items.name, lootdrop_entries.item_id, lootdrop_entries.lootdrop_id, lootdrop_entries.item_charges, lootdrop_entries.chance, loottable_entries.loottable_id, loottable_entries.multiplier, loottable_entries.probability from lootdrop_entries
inner join loottable_entries on lootdrop_entries.lootdrop_id = loottable_entries.lootdrop_id
inner join npc_types on npc_types.loottable_id = loottable_entries.loottable_id
inner join items on items.id = lootdrop_entries.item_id
where items.id = INSERT ITEM ID;
other useful queries:
Player Corpses by Name:
select player_corpses.charname, zone.short_name, player_corpses.x, player_corpses.y, player_corpses.z from player_corpses
inner join zone on zone.zoneidnumber = player_corpses.zoneid
where charname = 'INSERT PLAYER NAME';
What players have X item and how many:
select inventory.charid, character_.name, account.name, items.name, inventory.slotid, inventory.charges from inventory
inner join character_ on character_.id = inventory.charid
inner join account on account.id = character_.account_id
inner join items on items.id = inventory.itemid
where items.id = INSERT ITEM ID order by account.name;
|
Threaded Mode
