title: SQL multiple characteristic select on Sub Query
body: <p>self taught, first time poster.<br>
  I have what seems like it should be basic, but I am having a brain block on getting this to work.  I have two data sets, I want to compare one to the other and find the records that are in Set 1 (Actuals) that are not in set to (Operating Expenses).</p>
  
  <pre><code>select distinct vacctcode, vEntityID 
  from     tm1_fin_lines_integration 
  where    vScenario = '507'
    and    vLineItemType = 'OperatingExpense'
  </code></pre>
  
  <p>This returns a data set that looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/s6ss8.png" alt="Operating Expense Accounts"></p>
  
  <p>My second query is:</p>
  
  <pre><code>select i.property_externalid, i.externalid, r.lineitemtypename
  from ivw_Actuals i 
  inner join rvw_accounts r on r.accountnumber = i.externalid
  where r.LineItemTypeName = 'OperatingExpense'
  and i.PropertyAssetId in .
  (select vpropid from tm1_fin_lines_integration where vScenario = '507')
  </code></pre>
  
  <p>and returns results like:</p>
  
  <p><img src="https://i.stack.imgur.com/uJSVI.png" alt="Actuals"></p>
  
  <p>I want to find the records that are in the Actuals result set that are not in the Operating Expense Result Set.  I need to match on externalid/vacctcode and vEntityid/Property_ExternalID.</p>
  
  <p>I am just not sure how to match on multiple characteristics.  </p>
  
  <p>This query works when I am only looking at one vEntityid/Property_ExternalID, but in practice I will be looking at a data set of hundreds, so I need to be able to find the account code/entity combinations that are in the actuals set that are not in the Operating expense set.</p>
  
  <pre><code>select i.property_externalid, 
  i.externalid, 
  r.lineitemtypename
  from ivw_Actuals i 
  inner join rvw_accounts r on r.accountnumber = i.externalid
  where r.LineItemTypeName = 'OperatingExpense'
  and i.PropertyAssetId in 
  (select vpropid from tm1_fin_lines_integration where vScenario = '507')
  and i.externalid not in
  (select distinct vacctcode, vEntityID 
  from 
  tm1_fin_lines_integration 
  where 
  vScenario = '507'
  and vLineItemType = 'OperatingExpense')
  </code></pre>