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.