sous-requête - la façon de se référer à la requête externe de la valeur

La requête ci-dessous fonctionne bien:

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

Je veux ajouter une colonne select pour le résultat ci-dessus en utilisant une sous-requête select qui est:

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

La sous-requête select a une clause where et j'ai besoin d'obtenir le tblcase.id qui est la première colonne de l'ensemble de résultats. comment j'référence à cette valeur dans la sous-requête.

OriginalL'auteur user2492038 | 2013-06-17