Efficient Way to Clean Up Database (Items Mainly) by Zone?

[Kingly_Krab]

This may not be the cleanest way to do it, but here's a SQL query you can run to select ALL the item IDs and names from any zone's loot. This should make it a lot easier to find what items you're looking for.
# Is a placeholder for the shortname of the zone, i.e. akheva

SELECT DISTINCT i.id, i.`name` FROM items i
INNER JOIN lootdrop_entries lde ON i.id = lde.item_id
INNER JOIN loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
INNER JOIN npc_types n ON n.loottable_id = lte.loottable_id
INNER JOIN spawnentry se ON se.npcID = n.id
INNER JOIN spawngroup sg ON sg.id = se.spawngroupID
INNER JOIN spawn2 s2 ON s2.spawngroupID = sg.id
WHERE s2.zone = '#'
ORDER BY i.id ASC;

[Lane]

Awesome! Thank you both. I will try both approaches

[Lane]

Originally Posted by Kingly_Krab

This may not be the cleanest way to do it, but here's a SQL query you can run to select ALL the item IDs and names from any zone's loot. This should make it a lot easier to find what items you're looking for.
# Is a placeholder for the shortname of the zone, i.e. akheva

SELECT DISTINCT i.id, i.`name` FROM items i
INNER JOIN lootdrop_entries lde ON i.id = lde.item_id
INNER JOIN loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
INNER JOIN npc_types n ON n.loottable_id = lte.loottable_id
INNER JOIN spawnentry se ON se.npcID = n.id
INNER JOIN spawngroup sg ON sg.id = se.spawngroupID
INNER JOIN spawn2 s2 ON s2.spawngroupID = sg.id
WHERE s2.zone = '#'
ORDER BY i.id ASC;

This is literally the best thing I've seen in action... THANK YOU!!

[Kingly_Krab]

Originally Posted by Lane

This is literally the best thing I've seen in action... THANK YOU!!

INNER JOIN goes a really long way when it comes to linking tables and information.

[c0ncrete]

I need to remember AS as superfluous.