practical example queries (fully commented)

[c0ncrete]

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.

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)

+-----------------------------------------------+
| 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)