title: Join table in oracle database
body: <p>I have 2 tables that showing data <code>Item master</code> and <code>BOM</code>. I would like to join the tables between <code>Item master as T1</code> and <code>BOM as T2</code> and the additional table for table <code>BOM as T3</code>. Item master table containing <code>ITM_CD, ITM_TYP (1,2,3,4)</code> where each <code>ITM_TYP</code> represents a code for the first digit on <code>ITM_CD</code>. The thing that I want is like the picture below</p>
  <p><a href="https://i.stack.imgur.com/mSd5S.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mSd5S.png" alt="enter image description here" /></a></p>
  <p><strong>CHILD_CD2</strong> value replace to <strong>CHILD_CD1</strong> value. So the data should be like this. What query should I fix ? I am very new using oracle query.</p>
  <p><a href="https://i.stack.imgur.com/jGowU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jGowU.png" alt="enter image description here" /></a></p>
  <p>Here is mycode;</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT
      T1.ITM_CD,
      T2.C_ITM_CD AS CHILD_CD1,
      T3.C_ITM_CD AS CHILD_CD2
  FROM CM_HINMO_ALL T1
  INNER JOIN (SELECT P_ITM_CD, C_ITM_CD, BOM_PTN FROM SM_BOM_ALL) T2 
  ON T1.ITM_CD = T2.P_ITM_CD
          
  LEFT  JOIN (SELECT P_ITM_CD, C_ITM_CD, BOM_PTN FROM SM_BOM_ALL) T3 
  ON T2.C_ITM_CD = t3.P_ITM_CD
  WHERE 0=0
  AND T2.BOM_PTN IN (1)
  AND T1.ITM_TYP IN (1,2)
  AND T1.ITM_CD = '110100370'
  ORDER BY 2
  </code></pre>