EQEmulator Forums - List All Items In a Specified Zone Query

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Development::Database/World Building (https://www.eqemulator.org/forums/forumdisplay.php?f=596)

- - List All Items In a Specified Zone Query (https://www.eqemulator.org/forums/showthread.php?t=23596)

WildcardX

09-03-2007 12:50 PM

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

Foins

09-04-2007 01:13 PM

Works awesome thank you WildcardX

John Adams

09-04-2007 06:39 PM

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.

WildcardX

09-04-2007 08:52 PM

lol yeah thats funny.

gernblan

09-08-2007 05:21 AM

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.

Irreverent

09-11-2007 03:38 PM

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.

gernblan

09-16-2007 08:19 AM

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.

GeorgeS

09-17-2007 02:30 AM

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

gernblan

09-19-2007 11:08 PM

OH definitely. Definitely useful.

Xanathol

03-01-2019 06:31 PM

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.

All times are GMT -4. The time now is 07:48 AM.