title: How to get outstanding from two tables in sql
body: <p>I have two Tables: </p>
  
  <ol>
  <li>Sells  </li>
  <li>List item</li>
  </ol>
  
  <p>Receipt</p>
  
  <p>I have written Query but some of amount is added as it find in 2nd table</p>
  
  <p>1) <strong>Sells Table</strong>:</p>
  
  <pre><code>Create Table Sells(
          ID int,
          Invone varchar (30),
          InvDate DateTime ,
          Year varchar (15),
          Type varchar (10) ,
          Terms varchar (20) ,
          Days int ,
          DueDate DateTime,
          Pieces int ,
          Carats Real ,
          Price Real ,
          GrossAmt Real ,
          Discount1 Real ,
          DisAmt1 Real ,
          Discount2 Real,
          DisAmt2 Real ,
          FinalAmt Real,
          Party varchar (100), 
          Party_ID int,
          Person varchar (100),
          Shape varchar (30),
          Quality varchar (30),
          StockId int,
          SalesExe varchar (50),
          Remarks varchar (200), 
          CreatedDate DateTime,
          ModifiedDate DateTime,
          Username varchar (50))
  </code></pre>
  
  <p>2) <strong>Receipt Table:</strong></p>
  
  <pre><code>Create Table Receipt (
          ID int ,
          Invone varchar (30),
          RDate DateTime,
          Year varchar (15),
          Type varchar (10),
          HKD Real,
          Rate Real,
          ShortAmt Real,
          RecievedAmt Real,
          TotalRecievedAmt Real,
          FinalAmt Real,
          Party varchar (100),
          Party_ID int,
          Remarks varchar (200),
          CreatedDate DateTime,
          ModifiedDate DateTime,
          Username varchar (50)
          )
  </code></pre>
  
  <p>This is my Query:</p>
  
  <pre>
  
  
      select s.Invone,s.InvDate,s.Type,s.year,s.Terms,s.DueDate,s.Party,s.Party_ID,sum(s.Pieces) AS TotalPieces,
       round(sum(s.Carats), 3) AS TotalCarats, round(sum(s.GrossAmt), 2) AS TotalGrossAmt, round(sum(s.DisAmt1), 2) AS TotalDis1,
        round(sum(s.DisAmt2), 2) AS TotalDis2, round(sum(s.FinalAmt), 2) AS TotalFinalAmt, isnull(round(sum(HKD), 2), 0) AS TotalHKD,
         isnull(round(rate, 2), 0) AS Rate, isnull(round(sum(ShortAmt), 2), 0) AS TotalShort,isnull(round(sum(RecievedAmt), 2), 0) AS RecievedAmt,
          isnull(round(sum(TotalRecievedAmt), 2), 0) AS TotalRecievedAmt, round(sum(s.FinalAmt) - isnull(sum(TotalRecievedAmt), 0), 2) AS Balance
  
            from Sells s
       left join Recipt r on s.Invone = r.Invone WHERE s.Party = 'Buyer'
       group by s.Invone,s.InvDate,s.Party,s.Party_ID,s.Type,s.Year,s.Terms,s.DueDate,rate
       having  (sum(s.FinalAmt) - sum(isnull(TotalRecievedAmt, 0)))  0 
       order by s.InvDate, s.Invone
  
  </pre>
  
  <p>This is output i am getting
  <a href="http://i.stack.imgur.com/HKxqX.jpg" rel="nofollow">1</a>:<a href="http://i.stack.imgur.com/HKxqX.jpg" rel="nofollow">Image of Output</a></p>
  
  <p>I required Output Like Invone Should Come Once Only. and TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt should not get added 2 times or 3 times.</p>
  
  <p>if i have Invone no. 15 two times in sells table and three Receipt of 15 no. in part payment. then in output TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt should not added 3 times. it should show sum of TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt from sells table only.
  from Receipt Table it should show sum of TotalHKD, TotalShort, RecievedAmt,TotalRecievedAmt</p>