List All Items In a Specified Zone Query

[WildcardX]

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

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]

Works awesome thank you WildcardX

[John Adams]

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]

lol yeah thats funny.

[gernblan]

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]

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]

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]

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]

OH definitely. Definitely useful.

[Xanathol]

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:

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.