This is tough, what about..
SELECT DISTINCT(i.id), i.name, i.hp, i.mana, i.ac, sg.id, nt.name, s.zone FROM npc_types nt INNER JOIN spawnentry se ON se.npcid = nt.id INNER JOIN spawn2 s ON s.spawngroupid = se.spawngroupid INNER JOIN spawngroup sg ON sg.id = se.spawngroupid INNER JOIN loottable lt ON lt.id = nt.loottable_id INNER JOIN loottable_entries le ON le.loottable_id = lt.id INNER JOIN lootdrop_entries lde ON lde.lootdrop_id = le.lootdrop_id INNER JOIN items i ON i.id = lde.item_id WHERE s.zone = "ecommons" AND i.hp > 0;
This is tough, what about..
SELECT DISTINCT(i.id), i.name, i.hp, i.mana, i.ac, sg.id, nt.name, s.zone FROM npc_types nt INNER JOIN spawnentry se ON se.npcid = nt.id INNER JOIN spawn2 s ON s.spawngroupid = se.spawngroupid INNER JOIN spawngroup sg ON sg.id = se.spawngroupid INNER JOIN loottable lt ON lt.id = nt.loottable_id INNER JOIN loottable_entries le ON le.loottable_id = lt.id INNER JOIN lootdrop_entries lde ON lde.lootdrop_id = le.lootdrop_id INNER JOIN items i ON i.id = lde.item_id WHERE s.zone = "ecommons" AND i.hp > 0;