Global Item Drop Queries

[Uleat]

Ever wonder where to hunt for particular pieces of gear?

These two queries help sort locations and mob types for drop locations of global loot:

Query 1

select zone from spawn2 where spawngroupID
in (select spawngroupID from spawnentry where npcID
in (select id from npc_types where loottable_id
in (select loottable_id from loottable_entries where lootdrop_id
in (select lootdrop_id from lootdrop_entries where item_id
in (select id from items where id = '50095'
)))))
group by zone
order by zone;

Query 2

select name from npc_types where loottable_id
in (select loottable_id from loottable_entries where lootdrop_id
in (select lootdrop_id from lootdrop_entries where item_id
in (select id from items where id = '50095'
)))
and id
in (select npcID from spawnentry where spawngroupID
in (select spawngroupID from spawn2 where zone = 'citymist'
))
group by name
order by name;

To use, run Query 1 to find a list of zone 'short' names. Then, enter the desired name into the search criteria of Query 2.


The queries can be tailored to look for any item..you just need to adjust the 'items' sub-query to do so.

The 'items' search criteria for both queries need to match or you will not receive accurate results.

Query 2 groups like-named mobs together..so, you may need to remove that clause and add 'id' to the select criteria if
you believe that not all mobs with that name have this item in their global loot drop table.


These queries can probably be handled a lot better..but, they worked for what I needed at the time.


Note: The more complex the item query is, the longer it will take to finish - especially the zone search one.


Example results:

broodlands
burningwood
charasis
citymist
cobaltscar
corathus
crystal
dawnshroud
dreadlands
dulak
eastwastes
echo
emeraldjungle
Everfrost
firiona
frontiermtns
frozenshadow
fungusgrove
greatdivide
grimling
gukbottom
gunthak
hole
iceclad
jaggedpine
kael
karnor
katta
kedge
kithicor
lakeofillomen
letalis
lfaydark
mesa
mistmoore
moors
nadox
northkarana
northro
nurga
oceanoftears
overthere
permafrost
pojustice
rathemtn
scarlet
sebilis
shadeweaver
skyfire
skyshrine
soldunga
soldungb
southro
stonehive
tenebrous
thulehouse2
timorous
torgiran
trakanon
twilight
wakening

an_aberrant
an_apparition
a_berserk_fiend
a_fog_golem
a_haze_golem
a_lost_one
a_rambling_fanatic
a_raving_lunatic
a_savage_mutant
a_shadow
a_vapor_golem
greater_spurbone
gyrating_goo
plaguebone_skeleton

[Jahosphat]

Very cool. Ty

[GRUMPY]

Nice one Uleat. But it's time to scramble those letters around in your name - Ulate.
I could of seriously used this last night, haha

[Uleat]

Oh, doh! Sorry..was too lazy to just use #si and decided to actually see what dropped certain items

[GRUMPY]

Originally Posted by Uleat

Oh, doh! Sorry..was too lazy to just use #si and decided to actually see what dropped certain items

Haha, at the time, I was trying to find out which mobs may have had a manastone on them, but unfortunately it didn't exist
on any loot tables, so I had to do some googling before adding it to a few.

[Uleat]

If you prefer INNER JOINs...

SELECT `a`.`zone` FROM `spawn2` `a`
INNER JOIN `spawnentry` `b` ON `b`.`spawngroupID` = `a`.`spawngroupID`
INNER JOIN `npc_types` `c` ON `c`.`id` = `b`.`npcID`
INNER JOIN `loottable_entries` `d` ON `d`.`loottable_id` = `c`.`loottable_id`
INNER JOIN `lootdrop_entries` `e` ON `e`.`lootdrop_id` = `d`.`lootdrop_id`
INNER JOIN `items` `f` ON `f`.`id` = `e`.`item_id`
AND `f`.`id` = '10400' # `f`.`id` IN (...)
GROUP BY `a`.`zone`
ORDER BY `a`.`zone`;

SELECT `a`.`name` FROM `npc_types` `a`
INNER JOIN `loottable_entries` `b` ON `b`.`loottable_id` = `a`.`loottable_id`
INNER JOIN `lootdrop_entries` `c` ON `c`.`lootdrop_id` = `b`.`lootdrop_id`
INNER JOIN `items` `d` ON `d`.`id` = `c`.`item_id`
AND `d`.`id` = '10400' # `d`.`id` IN (...)
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `a`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID`
AND `f`.`zone` = 'mesa'
GROUP BY `a`.`name`
ORDER BY `a`.`name`;

I saw no difference in query time myself...


Note: Global items have changed in the way they are handled and no longer appear in loot tables. (Any remnants will eventually be taken out.)