title: SQL: Joining 3 tables with count, addition, and more restrictions
body: <p>Hei guys I'm really stuck on this problem. I want to get the total quantity (QT) which is Q1+Q2-Q3 with each Q(n) came from different tables. </p>
  
  <p>I have 3 tables</p>
  
  <pre><code>tables:  STOCKURP    REPORTENABLEURP    RECHARGE
  FK    :  code        code               accountid
  </code></pre>
  
  <p>Each tables have different amount of QUANTITY for each CREDIT, the first two tables, STOCKURP and REPORTENABLEURP, already have QUANTITY stored in them, while for the RECHARGE table, I have to count it first.</p>
  
  <p>To give you an easier understanding I'll divide this thing by parts.</p>
  
  <p><strong>Part 1: 2 Quantities</strong></p>
  
  <p>I want to get a list of the 2 quantities of all of the items in the 2 tables.
  This is my query:</p>
  
  <pre><code>select stockurp.code as CODE, stockurp.credit, stockurp.quantity as Q1, reportenableurp.qty as Q2
  from stockurp join reportenableurp 
  on stockurp.code = reportenableurp.code and stockurp.credit = reportenableurp.credit 
  where stockurp.date = '20150930' and reportenableurp.date = '2015-09-30'
  </code></pre>
  
  <p>With that, I get this</p>
  
  <pre><code>CODE  CREDIT   Q1   Q2
  12    15000    12   566
  12    20000    24   341
  43    15000    343  400
  43    20000    12   65
  46    50000    78   102
  </code></pre>
  
  <p><strong>Part 2: Counting the Q3</strong></p>
  
  <p>To get the third quantity (Q3), I have to count how many occurrences does an ACCOUNTID have for each CREDIT.
  My query is like this:</p>
  
  <pre><code>select accountid, credit, count (recharge.credit) as Q3
  from recharge
  group by accountid, credit
  </code></pre>
  
  <p>The result is this:</p>
  
  <pre><code>ACCOUNTID   CREDIT   Q3
     12       15000    20
     12       20000    301
     45       15000    65
     67       50000    9
  </code></pre>
  
  <p><strong>Part 3: QT The total quantity</strong></p>
  
  <p>QT = Q1 + Q2 - Q3</p>
  
  <p>To do so, I have to join the 3 tables and I cant seem to find a way to do this. I've tried multiple ways like:</p>
  
  <pre><code>select stockurp.code as CODE, stockurp.credit, Q1, Q2
  from 
       (
      select count (recharge.credit) as Q3
      from recharge
      group by accountid, credit
      )
  join 
      (
      select stockurp.quantity as Q1, reportenableurp.qty as Q2
      from stockurp join reportenableurp   
      on stockurp.code = reportenableurp.code and stockurp.credit = reportenableurp.credit 
      where stockurp.tanggal = '20150930' and reportenableurp.tanggal = '2015-09-30'
      )
  
  on stockurp.code = reportenableurp.code
  </code></pre>
  
  <p>Yeah I know, that query is stupid, lol.. I just cant find the right mindset to get the solution. The end result should be like this:</p>
  
  <pre><code>CODE  CREDIT   Q1   Q2   Q3   QT
  12    15000    12   566  34   544
  12    20000    24   341  124  241
  43    15000    343  400  87   656
  43    20000    12   65   50   27
  46    50000    78   102  11   169
  </code></pre>
  
  <p>Can you guys help me with this? Thanks in advance.</p>