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)