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.