title: SQL 'Invalid column name ' error, with inner select
body: <p>i have the following SQL:</p>
  
  <pre><code>SELECT A.*, 
  (SELECT answer FROM [tblAnswers] B 
  WHERE  B.memberID = A.memberID AND QuestionID = 3) AS ethnicity
  FROM [tblMembers] A  
  WHERE 
  LOWER(gender) = 'm' 
  AND ethnicity = 'Sephardi' 
  </code></pre>
  
  <p>and i get the error <code>Invalid column name 'ethnicity'</code> referring to the last reference to that column.</p>
  
  <p>Question: How come this column is not available to the <code>WHERE</code> clause?</p>
  
  <p>I know that if i do the following, it works:</p>
  
  <pre><code>SELECT A.*, 
  (SELECT answer FROM [tblAnswers] B WHERE  B.memberID = A.memberID AND QuestionID = 3) AS ethnicity
  FROM [tblMembers] A  
  WHERE
  LOWER(gender) = 'm' 
  AND convert(nvarchar, (SELECT answer FROM [tblAnswers] B WHERE  B.memberID = A.memberID AND QuestionID = 3)) = 'Sephardi'  
  </code></pre>