select
 r.ID
,r.type
,case r.KeyItem
 when 1984 then 'WOOD_PURIFICATION'
 when 1985 then 'WOOD_ENSORCELLMENT'
 when 1986 then 'LUMBERJACK'
 when 1987 then 'BOLTMAKER'
 when 1992 then 'METAL_PURIFICATION'
 when 1993 then 'METAL_ENSORCELLMENT'
 when 1994 then 'CHAINWORK'
 when 1995 then 'SHEETING'
 when 2000 then 'GOLD_PURIFICATION'
 when 2001 then 'GOLD_ENSORCELLMENT'
 when 2002 then 'CLOCKMAKING'
 when 2008 then 'CLOTH_PURIFICATION'
 when 2009 then 'CLOTH_ENSORCELLMENT'
 when 2010 then 'SPINNING'
 when 2011 then 'FLETCHING'
 when 2016 then 'LEATHER_PURIFICATION'
 when 2017 then 'LEATHER_ENSORCELLMENT'
 when 2018 then 'TANNING'
 when 2024 then 'BONE_PURIFICATION'
 when 2025 then 'BONE_ENSORCELLMENT'
 when 2026 then 'FILING'
 when 2032 then 'ANIMA_SYNTHESIS'
 when 2033 then 'ALCHEMIC_PURIFICATION'
 when 2034 then 'ALCHEMIC_ENSORCELLMENT'
 when 2035 then 'TRITURATION'
 when 2036 then 'CONCOCTION'
 when 2037 then 'IATROCHEMISTRY'
 when 2040 then 'RAW_FISH_HANDLING'
 when 2041 then 'NOODLE_KNEADING'
 when 2042 then 'PATISSIER'
 when 2043 then 'STEWPOT_MASTERY'
 when 2108 then 'VANADIEL_TRIBUNE_VOL21'
 when 0 then ''
 else r.keyItem
 end KI
,r.Alchemy AL
,r.Bone BN
,r.Cloth CL
,r.Cook CK
,r.Gold GO
,r.Leather LE
,r.Smith SM
,r.Wood WD
,concat(r.result,' x',r.ResultQty)  nq
,concat(r.resultHQ1,' x',r.ResultHQ1Qty) hq1
,concat(r.resultHQ2,' x',r.ResultHQ2Qty) hq2
,concat(r.resultHQ3,' x',r.ResultHQ3Qty) hq3
,replace((select name from item_basic where itemid=r.Crystal),'_crystal','') crystal
,replace((select name from item_basic where itemid=r.HQCrystal),'_crystal','') hq_crystal
,concat(ifnull((select name from item_basic where itemid=r.Ingredient1),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient2),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient3),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient4),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient5),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient6),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient7),''),'<br>'
,ifnull((select name from item_basic where itemid=r.Ingredient8),'')) ingredients
from synth_recipes r
where r.smith > 0
order by r.smith