A Few Queries for Matching Spells to Mobs...

**Uleat** · #1 10-28-2019, 09:54 PM

I was trouble-shooting a server crash and wanted to see if I could tie it to a specific spell or spell effect being used..so, I wrote a few queries.

Find list of spell effects used by npcs in a given zone (currently, filtered by `goodEffect`):

Code:

SELECT @zone_short := 'dranik';
SELECT @good_effect := '0';

SELECT `a`.`effectid1` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid2` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid3` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid4` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid5` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid6` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid7` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid8` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid9` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid10` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid11` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid12` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
ORDER BY `effect_id`;

Find list of spells that use a given effect within a zone (currently, filtered by `goodEffect`):

Code:

SELECT @zone_short := 'dranik';
SELECT @good_effect := '0';
SELECT @effect_id := '340';

SELECT `a`.* FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN  `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
AND @effect_id IN
(`a`.`effectid1`,`a`.`effectid2`,`a`.`effectid3`,`a`.`effectid4`,`a`.`effectid5`,`a`.`effectid6`,`a`.`effectid7`,`a`.`effectid8`,`a`.`effectid9`,`a`.`effectid10`,`a`.`effectid11`,`a`.`effectid12`)
GROUP BY `a`.`id`;

Find list of npcs that cast a given spell within a zone:

Code:

SELECT @zone_short := 'dranik';
SELECT @spell_id := '11839';

SELECT `a`.* FROM `npc_types` `a`
INNER JOIN `spawnentry` `b` ON `b`.`npcID` = `a`.`id`
INNER JOIN `spawn2` `c` ON `c`.`spawngroupID` = `b`.`spawngroupID` AND `c`.`zone` LIKE @zone_short
INNER JOIN `npc_spells` `d` ON `d`.`id` = `a`.`npc_spells_id`
INNER JOIN `npc_spells_entries` `e` ON `e`.`npc_spells_id` = `d`.`id` AND `e`.`spellid` = @spell_id;

Each query feeds the next one..but, the criteria will have to be manually updated.