title: Confused in join query in SQL
body: <p>The following works:</p>
  
  <pre><code>  SELECT IBAD.TRM_CODE, IBAD.IPABD_CUR_QTY, BM.BOQ_ITEM_NO, 
           IBAD.BCI_CODE, BCI.BOQ_CODE
      FROM IPA_BOQ_ABSTRCT_DTL IBAD, 
           BOQ_CONFIG_INF BCI,BOQ_MST BM
     WHERE BM.BOQ_CODE = BCI.BOQ_CODE 
       AND BCI.BCI_CODE = IBAD.BCI_CODE
       AND BCI.STATUS = 'Y' 
       AND BM.STATUS = 'Y'  
  order by boq_item_no;
  </code></pre>
  
  <p>Results:</p>
  
  <p><img src="https://i.stack.imgur.com/Uis4Q.gif" alt="enter image description here"></p>
  
  <p>But after joining many tables with that query, the result is confusing:</p>
  
  <pre><code> SELECT  (SELECT CMN_NAME 
              FROM CMN_MST 
             WHERE CMN_CODE= BRI.CMN_RLTY_MTRL) MTRL,
           RRI.RRI_RLTY_RATE AS RATE,
           I.BOQ_ITEM_NO,
           (TRIM(TO_CHAR(IBAD.IPABD_CUR_QTY,
               '9999999999999999999999999999990.999'))) AS IPABD_CUR_QTY,
           TRIM(TO_CHAR(BRI.BRI_WT_FACTOR,
               '9999999999999999999999999999990.999'))  AS WT,
           TRIM(TO_CHAR((IBAD.IPABD_CUR_QTY*BRI.BRI_WT_FACTOR),
               '9999999999999999999999990.999')) AS RLTY_QTY,
           (TRIM(TO_CHAR((IBAD.IPABD_CUR_QTY*BRI.BRI_WT_FACTOR*RRI.RRI_RLTY_RATE),
               '9999999999999999999999990.99'))) AS TOT_AMT,
           I.TRM_CODE AS TRM
    FROM
        (SELECT * FROM ipa_boq_abstrct_dtl) IBAD
  INNER JOIN 
        (SELECT * FROM BOQ_RLTY_INF) BRI
            ON IBAD.BCI_CODE = BRI.BCI_CODE 
  INNER JOIN
        (SELECT * FROM RLTY_RATE_INF) RRI
            ON BRI.CMN_RLTY_MTRL = RRI.CMN_RLTY_MTRL 
  INNER JOIN
        ( SELECT IBAD.TRM_CODE, IBAD.IPABD_CUR_QTY, 
                 BM.BOQ_ITEM_NO, IBAD.BCI_CODE, BCI.BOQ_CODE
            FROM IPA_BOQ_ABSTRCT_DTL IBAD, 
                 BOQ_CONFIG_INF BCI,BOQ_MST BM
           WHERE 
                 BM.BOQ_CODE = BCI.BOQ_CODE 
             AND BCI.BCI_CODE = IBAD.BCI_CODE
             and BCI.status = 'Y' 
            and bm.status = 'Y') I
            ON BRI.BCI_CODE = I.BCI_CODE 
           AND I.TRM_CODE = BRI.TRM_CODE
           AND BRI.TRM_CODE =4
  group by BRI.CMN_RLTY_MTRL, RRI.RRI_RLTY_RATE, I.BOQ_ITEM_NO, 
           IBAD.IPABD_CUR_QTY, BRI.BRI_WT_FACTOR, I.TRM_CODE, I.bci_code
  order by BRI.CMN_RLTY_MTRL
  </code></pre>
  
  <p>Results: </p>
  
  <p><img src="https://i.stack.imgur.com/1jS1O.gif" alt="enter image description here"></p>
  
  <p><code>TRM</code> should be <code>11</code> instead of <code>4</code> in the first row.</p>