title: how to get a different result set from the same column in one table
body: <p>i have a column in one table where i want to use it with two different where conditions.</p>
  <p>This is what i have</p>
  <pre><code>select t.Answer,
  (select top 1 t2.Answer
  FROM dbo.table1 t2
  INNER JOIN dbo.qa
  ON q.QuestionActionID = qa.QuestionActionID
  INNER JOIN dbo.Questions
  ON Questions.QuestionID = qa.QuestionID
  INNER JOIN dbo.Visits
  ON Visits.VisitID = q.VisitID
  
  Where
  
  Questions.QuestionID = 4583 
  and v.ID = 47543
  
  and qa.TypeID = 3 // equals
  and V.UserID='test'
  
  ) as comments
  FROM dbo.table1 t
  INNER JOIN dbo.qa
  ON t.QuestionActionID = qa.QuestionActionID
  INNER JOIN dbo.Questions
  ON Questions.QuestionID = qa.QuestionID
  INNER JOIN dbo.Visits v
  ON V.VisitID = t.VisitID
  Where
  Questions.QuestionID = 4583 
  and V.ID = 47543
  and V.UserID='test'
  and  qa.TypeID &lt;&gt; 3 //does not equal
  </code></pre>
  <p>which gives the following output</p>
  <pre><code>Answer  | comment
  12      | cup
  25      | cup
  </code></pre>
  <p>as you can see its adding &quot;cup&quot; to all of the comments because i said &quot;Top 1&quot;.
  Basically my sub query has a different type id value and my primary query has a not equal to type id value.</p>
  <p>My question is how do i get my sub query to return more than 1 value so that it matches the corresponding row.</p>
  <p>Note i can not use UNION ( this would have worked) because this query ends up been plugged into a larger query</p>