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 09-03-2007, 12:50 PM
WildcardX
Developer
 
Join Date: Apr 2003
Posts: 589
Default List All Items In a Specified Zone Query

Someone in the #eqemu room asked me to write a query to dump all items in a specified zone. Enjoy!

Disclaimer: Made this on MySQL v5.0.26

Code:
select distinct i.id, i.Name
from npc_types n
inner join spawnentry se on n.id = se.npcID
inner join spawn2 s on se.spawngroupID = s.spawngroupID
inner join loottable_entries lte on n.loottable_id = lte.loottable_id
inner join lootdrop_entries lde on lte.lootdrop_id = lde.lootdrop_id
inner join items i on lde.item_id = i.id
where s.zone = 'vexthal'
order by i.Name
__________________
Read my developer notes at my blog.

Quote:
If it's not on IRC, it ain't l33t!

Last edited by WildcardX; 09-03-2007 at 08:58 PM..
Reply With Quote
  #2  
Old 09-04-2007, 01:13 PM
Foins
Fire Beetle
 
Join Date: Sep 2006
Posts: 21
Default

Works awesome thank you WildcardX
Reply With Quote
  #3  
Old 09-04-2007, 06:39 PM
John Adams
Demi-God
 
Join Date: Jul 2006
Posts: 1,552
Default

Now that is truly scarey. I was building one of these exact queries not 5 minutes ago, and it looked damn near identical (including every one of your aliases lol).

I knew I liked you for a reason.
Reply With Quote
  #4  
Old 09-04-2007, 08:52 PM
WildcardX
Developer
 
Join Date: Apr 2003
Posts: 589
Default

lol yeah thats funny.
__________________
Read my developer notes at my blog.

Quote:
If it's not on IRC, it ain't l33t!
Reply With Quote
  #5  
Old 09-08-2007, 05:21 AM
gernblan
Discordant
 
Join Date: Aug 2006
Posts: 394
Default

Very helpful, thanks!

I had a query written that could do this, but it was, well let's just say I'd be too embarrassed to share it

This one is much better.
__________________
--
Keelyeh
Owner, ServerOp and Developer
Jest 4 Server
Linux (Jest3 runs on Fedora, our Dev servers usually run on Ubuntu and/or Gentoo), OC-12 Connection = Hella Fast
Reply With Quote
  #6  
Old 09-11-2007, 03:38 PM
Irreverent
The Solo Server
 
Join Date: May 2007
Posts: 416
Default

That's a much easier way, but might be nice to include a sub query/result of what mobs/zones also spawn the same items. Granted you don't care about how many places brigadine tunics drop, but it does help sometimes.
__________________
OP of Irreverent Server (The Solo Server)
Our Forums
Reply With Quote
  #7  
Old 09-16-2007, 08:19 AM
gernblan
Discordant
 
Join Date: Aug 2006
Posts: 394
Default

It would absolutely be great to know which mobs drop what easily, by item.

Another neat thing would be to compare to item tables from two databases and see only items in one database that are not in another.
__________________
--
Keelyeh
Owner, ServerOp and Developer
Jest 4 Server
Linux (Jest3 runs on Fedora, our Dev servers usually run on Ubuntu and/or Gentoo), OC-12 Connection = Hella Fast
Reply With Quote
  #8  
Old 09-17-2007, 02:30 AM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default

I suppose this query like what drops in each zone might be nice to be added into my npc/loot editor, as well as a few others like gernblan mentioned.

These sql are easy to write, but when they involve >1 subquery, I must use vb/net. In any event, would these type of tools be useful for server admins?

Perhaps, this would require a brand new tool, which displays item or npc drop stats...

GeorgeS
__________________
Your source for EQ database tools
Toolshop is open for business


http://www.georgestools.chrsschb.com//
Reply With Quote
  #9  
Old 09-19-2007, 11:08 PM
gernblan
Discordant
 
Join Date: Aug 2006
Posts: 394
Default

OH definitely. Definitely useful.
__________________
--
Keelyeh
Owner, ServerOp and Developer
Jest 4 Server
Linux (Jest3 runs on Fedora, our Dev servers usually run on Ubuntu and/or Gentoo), OC-12 Connection = Hella Fast
Reply With Quote
  #10  
Old 03-01-2019, 06:31 PM
Xanathol
Sarnak
 
Join Date: Oct 2009
Posts: 52
Default

Sorry for the bump on such an old thread but as I didn't see this elsewhere, it looked best to put this here.

If you are like me, the aforementioned query takes a long time. As such, please try the following, which seemed to perform better for me:

Code:
select distinct i.id, i.Name
from spawnentry se 
inner join (select * from spawn2 where zone = 'vexthal') as A on se.spawngroupID = A.spawngroupID
inner join npc_types n on n.id = se.npcID
inner join (select distinct lte.lootdrop_id, lte.loottable_id from loottable_entries lte) as B on n.loottable_id = B.loottable_id
inner join (select distinct lde.item_id, lde.lootdrop_id from lootdrop_entries lde) as C on B.lootdrop_id = C.lootdrop_id
inner join items i on C.item_id = i.id
order by i.Name
Basically a lil reordering to limit join results and a few distinct queries to limit redundant matches. Hope it helps someone.
Reply With Quote
Reply


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 05:16 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