practical example queries (fully commented)

c0ncrete · #1 03-26-2018, 11:54 AM

These are the sort of data analysis queries that will be handy to learn to compose when developing custom content.
NOTE: -- indicates a single-line comment (anything after will be ignored by MySQL)

PURPOSE: List active tradeskill recipes without resulting on_success item present in database.
NOTE: Demonstrates use of LEFT JOIN instead of a SELECT sub-query.

Code:

SELECT                               -- SELECT indicates these are the fields we want
  concat_ws(                         -- "join strings with separator"
    '|',                             -- separator to use
    tr.name,                         -- first string
    tre.recipe_id,                   -- second string
    tre.item_id                      -- third string
  ) AS 'item_name|recipe_id|item_id' -- joined column header
FROM                                 -- FROM lists tables to look for fields in
  tradeskill_recipe AS tr,           -- tr is now shorthand for tradeskill_recipe
  tradeskill_recipe_entries AS tre   -- tre is now shorthand for tradeskill_recipe_entries
LEFT JOIN items                      -- returns all matches from left table (tre),
ON tre.item_id = items.id            -- even if no match found on right table (items)
WHERE                                -- WHERE is how we filter results
  tre.recipe_id = tr.id AND          -- match recipe_id in tre and tr
  tre.componentcount < 1 AND         -- tre item is not a component
  tre.iscontainer < 1 AND            -- tre item is not a container
  items.id IS NULL;                  -- item_id not in items table (items shows NULL)

Code:

+-----------------------------------------------+
| item_name|recipe_id|item_id                   |
+-----------------------------------------------+
| Green Hued Gemstone|10334|0                   |
| Clockwork Scout Module|10344|0                |
| Dirty Green Gemstone|10346|0                  |
| Gem-headed Morningstar|10359|63593            |
| Gold-hafted Morningstar|10360|63588           |
| Gem-bladed Broad Spear|10361|63477            |
| Golden Gem-hafted Long Spear|10364|63426      |
| Platinum Gem-hafted Long Spear|10371|63441    |
| Golden Gem-hilted Bastard Sword|10387|58925   |
| Platinum Gem-hilted Bastard Sword|10393|58940 |
| Smoky Green Gemstone|10903|0                  |
| Red Dogwood Treant|13412|0                    |
| Essence of Rainfall|19908|0                   |
+-----------------------------------------------+
13 rows in set (0.52 sec)

c0ncrete · #2 03-26-2018, 11:55 AM

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)

c0ncrete · #3 03-26-2018, 01:38 PM

PURPOSE: Export a a list of items made by tradeskills into a CSV file for later use.
NOTE: no (idea what i was typing here...)

Code:

SELECT                             -- SELECT indicates these are the fields we want
  tre.item_id AS item_id,          -- item_id from tre table
  tr.name AS item_name             -- item_name from tr table
INTO OUTFILE                       -- indicates we are saving results to file
  "F:/ts_yields.csv"               -- full file path
FIELDS TERMINATED BY               -- field are separated by ...
  ','                              -- a comma, since this is a CSV
OPTIONALLY ENCLOSED BY             -- fields with whitespace enclosed in ...
 '"'                               -- double quotes
LINES TERMINATED BY                -- rows end in ... 
  '\n'                             -- a newline
FROM                               -- FROM lists tables to look for fields in
  tradeskill_recipe AS tr,         -- tr is now shorthand for tradeskill_recipe
  tradeskill_recipe_entries AS tre -- tre is now shorthand for tradeskill_recipe_entries
WHERE                              -- WHERE is how we filter results
  tre.recipe_id = tr.id AND        -- match recipe_id in tre and tr
  tre.componentcount < 1 AND       -- tre item is not a component
  tre.iscontainer < 1 AND          -- tre item is not a container
  tre.item_id > 0                  -- make sure the item number is valid
GROUP BY                           -- GROUP BY is used here to weed out duplicates
  tre.item_id                      --  (result set is 15008 rows with, 30248 without)
ORDER BY
  tre.item_id, tr.name;

Code:

1057,"Mystic Cloak"
1080,"Mithril Studded Skullcap"
1081,"Mithril Studded Mask"
1082,"Mithril Studded Gorget"
1083,"Mithril Studded Tunic"
1084,"Mithril Studded Shoulderpads"
1085,"Mithril Studded Cloak"
1086,"Mithril Studded Belt"
1087,"Mithril Studded Sleeves"
1088,"Mithril Studded Wristbands"
1089,"Mithril Studded Gloves"
1090,"Mithril Studded Leggings"
1091,"Mithril Studded Boots"
1095,"Case Of Tunarean Crests"
<SNIP!>
124684,"Warlord's Bane XVII"
124685,"Messenger's Bane XVII"
124686,"Quellious' Trauma XVII"
124687,"Solusek's Burn XVII"

c0ncrete · #4 03-31-2018, 05:07 AM

PURPOSE: List all Proving Grounds spawn group entries with a combined chance of less or greater than 100%,
NOTE: https://mariadb.com/kb/en/library/aggregate-functions/

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)
  SUM(se.chance) AS spawn_chance        -- sum of all chance field from spawnentry table (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
GROUP BY                                -- indicates we're looking at aggregate data
  spawngroup_id                         -- the field we're calculating via SUM in SELECT
HAVING                                  -- HAVING is used for filtering aggregate functions
  spawn_chance <> 100                   -- data filter (anything not 100%)
ORDER BY                                -- sort by these columns
  zone_sn,                              -- in order listed
  spawngroup_id;                        -- so it's easier to make sense of the data returned

Code:

+----------------+---------------+--------------+
| zone_sn        | spawngroup_id | spawn_chance |
+----------------+---------------+--------------+
| chambersa      |        259467 |          200 |
| chamberse      |        259477 |          200 |
| chambersf      |        259482 |          200 |
| provinggrounds |         43688 |          200 |
+----------------+---------------+--------------+
4 rows in set (0.01 sec)