[MySQL] テーブルの自己参照

テーブルの自己参照

Key,Valueでテーブルを乱立したくないとき、ひとつのテーブルにKeyID,Kye,Value,ValueGroupIDでひとつにまとめてしまうときなど。

SELECT
mst_items.id,
mst_items.itemID,
concat(
G_ClassBig.name,
'(', G_ClassMid.name, ') ',
G_From.name, '→', G_To.name
) as itemName,
FORMAT(mst_items.costIn,0) as CostIn,
FORMAT(mst_items.costOut,0) as CostOut
FROM
mst_items,
mst_vars as G_ClassBig,
mst_vars as G_ClassMid,
mst_vars as G_From,
mst_vars as G_To
WHERE
G_ClassBig.varGroup = 100 AND G_ClassBig.varID = substr(mst_items.itemID,1,3)
AND
G_ClassMid.varGroup = 101 AND G_ClassMid.varID = substr(mst_items.itemID,4,3)
AND
G_From.varGroup = 200 AND G_From.varID = substr(mst_items.itemID,7,3)
AND
G_To.varGroup = 210 AND G_To.varID = substr(mst_items.itemID,10,3)
ORDER BY
mst_items.itemID ASC