title: Self Joins instead of subqueries
body: <p>Can someone figure out how to get rid of the <code>NOT EXISTS</code> statements in the <code>WHERE</code> clause?</p>
  
  <pre><code>SELECT TOP 100 PERCENT 
      Ftm.AcctID AS [Acct Id], 
      Act.AccountNumber AS [Account No.], 
      Act.AccountTypeId, 
      ISNULL(Cnt.FirstName, '')+ ' ' + ISNULL(Cnt.LastName, '') AS [Full Name], 
      Ftm.FinTransTypeCode AS [Trans Type], 
      Ftm.FinTransCode AS [Trans Code], 
      Fm.FJNo AS [FJ No.],   
      Fm.ReversalFJNo  AS [Reversal FJNo.],  
      CAST(ISNULL(Fm.FJAmt,0) AS DECIMAL(9, 2))  AS Amount, 
      Ftc.InterfaceDescr AS [Transaction Desc], 
      Fm.Comments, Fm.CreatedBy AS [Posted By],   
      Ftm.Created,RegistrationTypeid, FJDate  
  FROM
      FinMaster AS Fm 
      INNER JOIN FinTransMaster AS Ftm ON Ftm.FjNo = Fm.FJNo 
      INNER JOIN dbo.Account AS Act ON Ftm.AcctID = Act.AccountId
      INNER JOIN dbo.Contact AS Cnt ON Act.PrimaryContactId = Cnt.ContactId
      INNER JOIN FinTransCodes AS Ftc ON Ftc.FinTransCode = Ftm.FinTransCode
  WHERE     
      (Ftm.FinTransTypeCode &lt;&gt; 'PYMT') AND FJDate &gt; getdate()-5  
      AND (NOT EXISTS (SELECT '' AS Expr1  
                       FROM FinMaster  
                       WHERE (ReversalFjNo = Fm.FJNo))) 
      AND (NOT EXISTS (SELECT '' AS Expr1  
                       FROM FinTransMaster AS Ftm2  
                       WHERE (FjNo = Ftm.FjNo) AND (FjTransSeqNo &lt; Ftm.FjTransSeqNo)))  
  ORDER BY Ftm.Created DESC 
  </code></pre>