title: How to use a newly defined column name in the where clause of a query in sql 2008
body: <p>I have a query in sql server 2008 like:</p>
  
  <pre><code>select t1.Id, 
  MatchIds = (select dbo.GetCommaDelimitedString(t2.Id) from #temp t2
         where t1.Id != t2.Id and t1.PatientId=t2.PatientId 
         and t1.HCN=t2.HCN 
        )
  from #temp t1
  </code></pre>
  
  <p>This query has an output like:</p>
  
  <pre><code>Id   MatchIds
  1    2,5,6
  2    1,5,6
  3    null
  4    null
  5    1,2,6
  6    1,2,5
  </code></pre>
  
  <p>What I want is to get rid of the rows whose MatchIds is null. When I try to add <code>MatchIds is not null</code> with a where clause to the main query, it is not accepting it saying that <code>invalid column name MatchIds</code>and I do not want to write the same query I used to assign MatchIds in the where clause as well. In this case, what is the best way to provide it?</p>
  
  <p>Any help would be appreciated.</p>