title: Subtracting two columns from two different table with different data
body: <p>i had this problem where i got two tables with different values.</p>
  
  <p>TABLE 1:</p>
  
  <pre><code>Description    Qty   Amount
  Proc 1          1     100
  Proc 2          1      50
  Proc 3          1      60
  </code></pre>
  
  <p>TABLE 2:</p>
  
  <pre><code>Description     Payment
    Proc 1          60
    Proc 1          30
    Proc 2          20
    Proc 3          60
    Proc 2          20
  </code></pre>
  
  <p>So, the result should be like:</p>
  
  <pre><code>Description     Balance
    Proc 1          10
    Proc 2          40
    Proc 3           0
  </code></pre>
  
  <p>How will i do this with select query? thanks.</p>
  
  <p>I Already tried this code but it should first get the distinct rows of TABLE1 and sum distinct values from TABLE2 before subtracting the two tables.</p>
  
  <pre><code>SELECT FEESList.[Fee Description], sum(StudentBILLING.Quantity* StudentBILLING.Total- isnull(StudReceipts.Amount,0)) as Balance
  FROM StudentBILLING INNER JOIN FEESList ON StudentBILLING.FeeID = FEESList.FeeID INNER JOIN SREGStudentInformation ON StudentBILLING.StudentID = SREGStudentInformation.ID INNER JOIN
        SemesterList ON StudentBILLING.SemesterID = SemesterList.SemID INNER JOIN SchoolYear ON StudentBILLING.SYid = SchoolYear.[SY ID] FULL JOIN StudReceipts ON FEESList.FeeID = StudReceipts.FeeID
  WHERE (SREGStudentInformation.[Student ID] = @StudentNumber) AND (SemesterList.[Sem.] = @Sem) AND (SchoolYear.[School Year] = @SchoolYear) AND 
        (FEESList.[Type of Fee] = 2) AND (StudentBILLING.Quantity* StudentBILLING.Total- ISNULL(StudReceipts.Amount,0))&gt;0
  GROUP BY FEESList.[Fee Description]
  </code></pre>
  
  <p>The result is from this;</p>
  
  <pre><code>Description    Amount   Payments
      Proc 1      100       60
      Proc 1      100       30
      Proc 2      50        20
      Proc 2      50        20
      Proc 3      60        60
  </code></pre>
  
  <p>To this result which is not supposed to be.</p>
  
  <pre><code>Description    Amount   
      Proc 1      100     
      Proc 2      50   
  </code></pre>
  
  <p>Can you help me with this?</p>