title: How to find differences between two queries that do not have a uniqueID
body: <p>I was using the following query to try to find the differences between the results of a queries of two databases.  I am not able to use the AccountID as a compare point since they don't line up between the different databases.  The selects in the ctes both return 200105 rows but the result of the join on the cte has 201779 rows.  The data in this case should be able to match up. Am I on the right track with this?  If so what am I missing, or should I look to a different approach.</p>
  
  <pre><code>    with cteDev3 (AccountID, SecurityID, UniqueAccountID, BuyPrice, BuyDate, Shares)as
        (select Al.AccountID, al.SecurityID,ad.UniqueAccountId, BuyPrice, BuyDate, Shares
          from dev3TMAd.dbo.AccountLot al
          join dev3TMAd.dbo.AccountDetails ad
          on al.AccountID = ad.AccountId
          where ad.EnterpriseId = 'HuberFinancial'),
      cteTest2 (AccountID, SecurityID, UniqueAccountID, BuyPrice, BuyDate, Shares)as
         (select Al.AccountID, al.SecurityID,ad.UniqueAccountId, BuyPrice, BuyDate, Shares
          from test2TMAd.dbo.AccountLot al
          join test2TMAd.dbo.AccountDetails ad
          on al.AccountID = ad.AccountId
          where ad.EnterpriseId = 'HuberFinancial')
  
  select c3.UniqueAccountId as 'Dev3' , t2.UniqueAccountId as 'Test2'
  from cteTest2 t2
  join cteDev3 c3
      on c3.UniqueAccountId = t2.UniqueAccountId
      and c3.SecurityID = t2.SecurityID
      and c3.BuyDate = t2.BuyDate
      and c3.Shares = t2.Shares
      and c3.BuyPrice = t2.BuyPrice
  </code></pre>
  
  <p>This is what the result sets look like for the subqueries that are part of the ctes:</p>
  
  <pre><code>AccountID | SecurityID | UniqueAccountId | BuyPrice | BuyDate | Shares
  949662 | 17030 | 11284035 | 42.690021 | 2007-12-19 00:00:00 | 4.710000
  949662 | 17030 | 11284035 | 42.690629 | 2007-12-19 00:00:00 | 13.521000
  949662 | 17030 | 11284035 | 42.611940 | 2007-12-19 00:00:00 | 0.134000
  949662 | 17030 | 11284035 | 39.323467 | 2008-03-10 00:00:00 | 0.946000
  949662 | 17030 | 11284035 | 40.831884 | 2008-06-10 00:00:00 | 6.323000
  949662 | 17030 | 11284035 | 30.730860 | 2008-09-09 00:00:00 | 6.335000
  949662 | 17030 | 11284035 | 16.290063 | 2008-12-10 00:00:00 | 111.045000
  950091 | 25885 | 11937183 | 14.629975 | 2012-03-23 00:00:00 | 12.337000
  950091 | 25885 | 11937183 | 14.599671 | 2012-04-23 00:00:00 | 12.140000
  950091 | 4325 | 11937183 | 11.082955 | 2012-01-27 00:00:00 | 6768.953000
  950091 | 4325 | 11937183 | 11.119163 | 2012-01-31 00:00:00 | 1.242000
  </code></pre>