WITH table_a AS
(
SELECT '001' categoryCode, '상의' categoryName
UNION ALL SELECT '001001', '후드티'
UNION ALL SELECT '001001002', '겨울'
)
, table_b AS
(
SELECT 100 itemNo, '001001002' categoryCode
UNION ALL SELECT 200, '001001002'
)

-- 1. 코드 테이블과 세번 조인하는 방법
SELECT m.itemNo
     , m.categoryCode
     , c1.categoryName categoryName1
     , c2.categoryName categoryName2
     , c3.categoryName categoryName3
  FROM table_b m
  LEFT OUTER JOIN table_a c1 ON c1.categoryCode = SUBSTR(m.categoryCode, 1, 3)
  LEFT OUTER JOIN table_a c2 ON c2.categoryCode = SUBSTR(m.categoryCode, 1, 6)
  LEFT OUTER JOIN table_a c3 ON c3.categoryCode = SUBSTR(m.categoryCode, 1, 9)
 WHERE m.categoryCode = '001001002'
;

-- 2. 한번에 조인(등호가 아닌 LIKE 조건) 후 그룹바이 하는 방법
SELECT m.itemNo
     , m.categoryCode
     , MIN(CASE LENGTH(c.categoryCode) WHEN 3 THEN c.categoryName END) categoryName1
     , MIN(CASE LENGTH(c.categoryCode) WHEN 6 THEN c.categoryName END) categoryName2
     , MIN(CASE LENGTH(c.categoryCode) WHEN 9 THEN c.categoryName END) categoryName3
  FROM table_b m
  LEFT OUTER JOIN table_a c
--  ON m.categoryCode LIKE CONCAT(c.categoryCode, '%')
    ON INSTR(m.categoryCode, c.categoryCode) = 1
 WHERE m.categoryCode = '001001002'
 GROUP BY m.itemNo, m.categoryCode
;