title: Condense TSQL statement with a double layer group by having count(*) =
body: <p>An thoughts on how to condense this query?</p>
  
  <pre><code>select [gp1].[sID] 
  from
  ( 
     select [ftsIndexWordOnce].[sID], [ftsIndexWordOnce].[wordID]
       from [ftsIndexWordOnce] with (nolock)
       Join [FTSindex] with (nolock) 
         On [FTSindex].[sID] = [ftsIndexWordOnce].[sID] 
        And [FTSindex].[wordID] = [ftsIndexWordOnce].[wordID] 
        And [FTSindex].[wordPOS] &lt;= '1000' 
       join [FTSwordDef] with (nolock) 
         on [ftsIndexWordOnce].[wordID] = [FTSwordDef].[ID] 
        and [FTSwordDef].[word] in ('capital','bank')   
      group by [ftsIndexWordOnce].[sID], [ftsIndexWordOnce].[wordID]
  )   [gp1] 
  group by [gp1].[sID] 
  having count(*) = 2
  </code></pre>
  
  <p>PK [ftsIndexWordOnce] is [sID], [wordID]<br>
  PK [FTSindex] is [sID], [wordPos] </p>
  
  <p>Below is not part of the question.<br>
  It is just background.  </p>
  
  <p>The base query is  </p>
  
  <pre><code>select [ftsIndexWordOnce].[sID] 
   from [ftsIndexWordOnce] with (nolock)
   join [FTSwordDef] with (nolock) 
     on [ftsIndexWordOnce].[wordID] = [FTSwordDef].[ID] 
    and [FTSwordDef].[word] in ('capital','bank')
  group by [ftsIndexWordOnce].[sID]
  having count(*) = 2 
  order by [ftsIndexWordOnce].[sID]
  </code></pre>
  
  <p>This finds document with both the words.<br>
  The question query extends this to both those words in the 1000 words.   </p>