title: join table with prefix and sum
body: <p>I have two mysql tables fk_orders &amp; fk_payments. Orders table column order_item id is without prefix of 'OI:' need to relate it with payment table which has order_item_id with 'OI:' prefix, Need to sum settlement value + refund and group them by order_item_id with prefix</p>
  
  <p>fk_orders</p>
  
  <pre><code>|order_item_id |order_id    |Invoice_No       |Invoice_No_Amt  |Qty   |Refund_Qty |Refund_Amount
  ------------------------------------------------------------------------------------------------
  |1131231       |123         |F08OTTN16-1      |100            |1     |            |
  |1113138       |321         |F08OTTN16-2      |200            |2     |1           |200
  |1231231       |023         |F08OTTN16-3      |100            |1     |1           |100
  |1133138       |320         |F08OTTN16-4      |200            |2     |            |
  |1134231       |103         |F08OTTN16-5      |100            |1     |            |
  |1113538       |300         |F08OTTN16-6      |200            |2     |            |
  |1003538       |300         |F08OTTN16-7      |200            |2     |            |
  </code></pre>
  
  <p>fk_payments</p>
  
  <pre><code>|order_item_id    |order_id    |Invoice_No       |Invoice_No_Amt |Settlement_Value
  -----------------------------------------------------------------------------------
  |OI:1131231       |123         |F08OTTN16-1      |100            |40
  |OI:1113138       |321         |F08OTTN16-2      |200            |150
  |OI:1231231       |023         |F08OTTN16-3      |100            |-50
  |OI:1133138       |320         |F08OTTN16-4      |200            |200
  |OI:1134231       |103         |F08OTTN16-5      |100            |40
  |OI:1113538       |300         |F08OTTN16-6      |200            |250
  |OI:1131231       |123         |F08OTTN16-1      |100            |40
  |OI:1133138       |320         |F08OTTN16-4      |200            |100
  |OI:1113138       |321         |F08OTTN16-2      |200            |-200
  </code></pre>
  
  <p>query result to be like the below</p>
  
  <pre><code>|order_item_id    |order_id    |Invoice_No       |Invoice_No_Amt |Qty   |Final_Settled_Amt 
                                                                           (refund_amount + 
                                                                           (sum of settled value group by order_item_id)
  ---------------------------------------------------------------------------------------
  |OI:1131231       |123         |F08OTTN16-1      |100            |1     |80
  |OI:1113138       |321         |F08OTTN16-2      |200            |2     |150
  |OI:1231231       |023         |F08OTTN16-3      |100            |1     |50
  |OI:1133138       |320         |F08OTTN16-4      |200            |2     |300
  |OI:1134231       |103         |F08OTTN16-5      |100            |1     |400
  |OI:1113538       |300         |F08OTTN16-6      |200            |2     |250
  |OI:1003538       |300         |F08OTTN16-7      |200            |2     |0 
  </code></pre>