title: How to reference subquery in the WHERE clause
body: <p>I have a SQL query (T-SQL) in which I have a subquery in the <code>SELECT</code> statement.</p>
  
  <p>Here is a reduced version:</p>
  
  <pre><code>SELECT 
      p.id AS id,
      (SELECT
          jobs_without_price.id
      FROM
          st_job jobs_without_price
      WHERE
          jobs_without_price.person_id = p.id
      AND
          jobs_without_price.price IS NULL
      FOR XML AUTO, ROOT('jobs')) AS JobsWithoutPrice
  FROM
      st_person p
  WHERE
      JobsWithoutPrice IS NOT NULL
  GROUP BY 
      p.id
  </code></pre>
  
  <p>The problem is that SSMS tells me</p>
  
  <blockquote>
    <p>Invalid column name 'JobsWithoutPrice'.</p>
  </blockquote>
  
  <p>This is almost certainly because SQL doesn't allow aliases in <code>WHERE</code> clauses, but how else can I use the result of this subquery to filter the results?</p>
  
  <p>[The full query has more table joins and much more in the <code>SELECT</code> statement, all of which pertains to a single <em>person</em> record, whereas the subquery reports back multiple records, which is why it's in a subquery.]</p>