title: Filter out rows returned in subquery
body: <p>I have a temp table that stores rollup data and would like to create a query to see if I could clean up the data a little bit.  I thought it would be pretty easy but it has turned out to be much more difficult.  Maybe it is easy and I'm just approaching it all wrong.</p>
  
  <p>What I'm trying to do is simply remove rows where Item = 'TOTALS' but where a name only shows up twice. So in the table below, rows 7 and 9 should be removed.  Just so you know, there are no keys.</p>
  
  <p>Name . Item ......Dlvrd<br/>
  --------------------------<br/>
  Bob .       102 ..........          18<br/>
  Bob .       106 ..........          32<br/>
  Bob .       108 ..........          34<br/>
  Bob .       999 ..........          184<br/>
  Bob ..      TOTALS .    702<br/>
  John .      64 ...........          86<br/>
  John .      TOTALS .    86<br/>
  Jim  ..     112 ..........          131<br/>
  Jim  ..     TOTALS ..   131<br/>
  Jane .      10 ..........           720<br/>
  Jane .      12 ........ .       217<br/>
  Jane .      999 ........            867<br/>
  Jane .      TOTALS  1848<br/>
  </p>
  
  <p>My first attempt consisted of adding a column with the count of the name column, then using a where clause like:  where name != '2' and Item != 'TOTALS'.  Didn't work, even putting parenthesis around them.  It just removes any row with a 2 or where Item = 'TOTALS'.  It's totally ignoring the 'AND'.  Here my sql:</p>
  
  <pre><code>select t.count, tr.*
  From #TempRollup tr
  join (
      select 
        Name
      , COUNT(Name) count
      from #TempRollup
      Group by Name
      ) as t on t.Name = tr.Name
  where (
  t.count != 2
  and Item != 'TOTALS'
  )
  </code></pre>
  
  <p>Then I tried this method that involves creating a subquery to find the rows I want to ignore and then just not selecting them.  My subquery grabs the correct rows but when the whole thing is run, nothing is returned.:</p>
  
  <pre><code>Select *
  FROM #TempRollup
  WHERE NOT EXISTS(  
      select tr.*
      From #TempRollup tr
      join (
              select 
                Name
              , COUNT(Name) count
              from #TempRollup
              Group by Name
              ) as t on t.Name = tr.Name
      where t.count = 2
      and Item = 'TOTALS'
  )
  </code></pre>