title: subquery - how to refer to outer query value
body: <p>The query below is working fine:</p>
  
  <pre><code>SELECT 
     tblCase.ID AS CaseID, tblCase.UserID AS MyCasesFilter, 
     tblGroupMembership.UserID AS GroupShareFilter, 
     tblDirectCaseSharing.ReceiverUserID AS DirectShareFilter, tblCase.EntryDate, 
     tblUser.LastName AS CaseAuthor, tblCase.Name AS CaseName, 
     COUNT(DISTINCT tblCaseImage.ID) AS TotalImages, 
     tblCaseType.Name AS CaseType, tblCase.SiteName, tblCase.Category, 
     tblCase.FollowUpDateTime, tblCase.Notes
  FROM     
     tblDirectCaseSharing 
  RIGHT OUTER JOIN
     tblCase 
  INNER JOIN tblUser ON tblCase.UserID = tblUser.ID ON tblDirectCaseSharing.CaseID = tblCase.ID 
  LEFT OUTER JOIN
     tblGroupMembership 
  INNER JOIN
     tblGroupCase ON tblGroupMembership.GroupID = tblGroupCase.GroupID ON tblCase.ID = tblGroupCase.CaseID 
  LEFT OUTER JOIN
     tblCaseType ON tblCase.CaseTypeID = tblCaseType.ID 
  LEFT OUTER JOIN
     tblCaseImage ON tblCase.ID = tblCaseImage.CaseID
  GROUP BY 
     tblCase.ID, tblCaseType.Name, tblCase.SiteName, tblCase.EntryDate, 
     tblCase.Category, tblCase.FollowUpDateTime, tblCase.Notes, tblCase.UserID, 
     tblGroupMembership.UserID, tblDirectCaseSharing.ReceiverUserID, 
     tblUser.LastName, tblCase.Name
  HAVING 
     (tblCase.UserID = 1) 
     AND (tblGroupMembership.UserID = 2) 
     AND (tblDirectCaseSharing.ReceiverUserID = 3)
  ORDER BY 
     tblCase.EntryDate DESC
  </code></pre>
  
  <p>I want to add an additional select column to the above result using a select subquery which is:</p>
  
  <pre><code>STUFF((
  SELECT ', ' +tblGroup.Name  as [text()] 
  FROM     tblCase INNER JOIN
                    tblGroupCase ON tblCase.ID = tblGroupCase.CaseID INNER JOIN
                    tblGroup ON tblGroupCase.GroupID = tblGroup.ID
  WHERE tblCase.ID = ***
  FOR XML PATH('')
  ),1,2,'')
  AS ConcatGroupShares
  </code></pre>
  
  <p>The select subquery has a where clause and I need to get the tblcase.id which is the first column of the result set. how to i reference that value in the subquery.</p>