title: Multiple INNER JOIN with GROUP BY and Aggregate Function
body: <p>I'm back with another question. I've been tinkering with this for 1 and a half days now and still no luck. So I have the tables below.</p>
  
  <pre><code>Table1
  Field1 Field2 Field3 Field4     Field5
  DR1    500    ID1    Active     TR1
  DR2    250    ID2    Active     TR1
  DR3    100    ID1    Active     TR1
  DR4    50     ID3    Active     TR1
  DR5    50     ID1    Cancelled  TR1
  DR6    150    ID1    Active     TR2
  
  Table2
  Field1 Field3
  ID1    Chris
  ID2    John
  ID3    Jane
  
  Table3
  Field1 Field2
  TR1    Shipped  
  TR2    Pending
  </code></pre>
  
  <p>I currently can achieve this result.</p>
  
  <pre><code>Name   Total
  Chris  650    3
  John   250    1
  Jane   50     1
  </code></pre>
  
  <p>using this sql statement </p>
  
  <pre><code>SELECT t2.Field3 as Name , SUM(t1.Field2) as Total
   FROM [Table1] t1 INNER JOIN [Table2] t2 ON t1.Field3 = t2.Field1
    GROUP BY t2.Field3
  </code></pre>
  
  <p>However, I'd like to achieve this result shown below.</p>
  
  <pre><code>Chris 600 2
  John  250 1
  Jane  50  1
  </code></pre>
  
  <p>I'd like to check Table3 first if it has a 'Shipped' Field2 then it includes everything in Table1 with 'Active' Field4. It should not include 'Cancelled' Field4. And if Table3 has a Field2 of Pending, it should also not include it. I'd appreciate any little help. Thank you.</p>