PURPOSE: List info for active Proving Ground spawns (NOT scripted).
NOTE: Use SUM to combine chances to find groups with <> 100% total.
Code:
SELECT -- SELECT indicates these are the fields we want
s2.zone AS zone_sn, -- zone shortname from spawn2 (aliased)
s2.spawngroupID AS spawngroup_id, -- spawngroupID field from spawn2 table (aliased)
se.chance AS spawn_chance, -- chance field from spawnentry table (aliased)
se.npcID AS npc_id, -- npc_id from spawnentry table (aliased)
nt.name AS npc_name, -- npc_name from npc_types table (aliased)
s2.x, s2.y, s2.z -- x, y, z coords from spawn2 (not aliased)
FROM -- FROM lists tables to look for fields in
spawn2 AS s2, -- s2 is now shorthand for spawn2 table
spawnentry AS se, -- se is now shorthand for spawnentry table
npc_types AS nt -- nt is now shorthand for npc_types table
WHERE -- WHERE is how we filter results
( -- group similar conditions in parentheses
zone LIKE "chambers%" OR -- zone short name matches chambers* OR
zone = 'provinggrounds' -- zone short name is 'provinggrounds'
) AND -- end of grouping
se.spawngroupID = s2.spawngroupID AND -- match spawngroup_id in spawnentry and spawn2
se.npcID = nt.id AND -- match npc_id in spawnentry and npc_types
s2.enabled = 1 -- spawn2 entry enabled
ORDER BY -- sort by these columns
zone_sn, -- in order listed
spawngroup_id, -- so it's easier to make sense of
npc_name; -- the data returned
Code:
+----------------+---------------+--------------+--------+--------------------------------+--------------+--------------+-------------+
| zone_sn | spawngroup_id | spawn_chance | npc_id | npc_name | x | y | z |
+----------------+---------------+--------------+--------+--------------------------------+--------------+--------------+-------------+
| chambersa | 42456 | 50 | 304005 | #a_fearless_Dragorn | -160.000000 | 386.000000 | 59.875000 |
| chambersa | 42456 | 50 | 304009 | a_dragorn | -160.000000 | 386.000000 | 59.875000 |
| chambersa | 42457 | 50 | 304011 | #a_fearless_dire_wolf | -166.596725 | 348.648071 | 52.171665 |
| chambersa | 42457 | 50 | 304006 | a_dire_wolf | -166.596725 | 348.648071 | 52.171665 |
| chambersa | 42458 | 50 | 304010 | #a_fearless_elemental | -252.000000 | 162.000000 | 59.625000 |
| chambersa | 42458 | 50 | 304007 | an_elemental | -252.000000 | 162.000000 | 59.625000 |
| chambersa | 42459 | 50 | 304012 | #a_fearless_skeleton | -160.000000 | 162.000000 | 59.625000 |
| chambersa | 42459 | 50 | 304008 | a_cackling_skeleton | -160.000000 | 162.000000 | 59.625000 |
| chambersa | 42460 | 100 | 304004 | Master_of_Fear | -212.000000 | 273.000000 | 70.000000 |
<SNIP>
| provinggrounds | 275045 | 80 | 316001 | a_huvul_commander | 2383.350098 | -943.820007 | -306.529999 |
| provinggrounds | 275045 | 20 | 316070 | Strategist_Kin`Muram | 2383.350098 | -943.820007 | -306.529999 |
+----------------+---------------+--------------+--------+--------------------------------+--------------+--------------+-------------+
805 rows in set (0.03 sec)