Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Database/World Building

Development::Database/World Building World Building forum, dedicated to the EQEmu MySQL Database. Post partial/complete databases for spawns, items, etc.

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2017, 06:14 PM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default Global Item Drop Queries

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
Code:
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
Code:
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:
Code:
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
Code:
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
__________________
Uleat of Bertoxxulous

Compilin' Dirty

Last edited by Uleat; 03-02-2017 at 11:36 PM..
Reply With Quote
  #2  
Old 03-02-2017, 10:28 PM
Jahosphat
Sarnak
 
Join Date: Apr 2009
Location: LA
Posts: 96
Default

Very cool. Ty
Reply With Quote
  #3  
Old 03-02-2017, 10:38 PM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

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
Reply With Quote
  #4  
Old 03-02-2017, 11:35 PM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default

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

Compilin' Dirty
Reply With Quote
  #5  
Old 03-03-2017, 12:22 AM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

Quote:
Originally Posted by Uleat View Post
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.
Reply With Quote
  #6  
Old 10-05-2019, 01:10 AM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default

If you prefer INNER JOINs...

Code:
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`;
Code:
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.)
__________________
Uleat of Bertoxxulous

Compilin' Dirty

Last edited by Uleat; 10-05-2019 at 01:28 AM..
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 10:11 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3