Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

subquery - how to refer to outer query value

The query below is working fine:

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

I want to add an additional select column to the above result using a select subquery which is:

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

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.

like image 603
user2492038 Avatar asked Jun 17 '13 04:06

user2492038


People also ask

Which subquery use value FROM outer query?

In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.

What type of subquery contains a reference to a column in the outer query?

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.

Can subquery retrieve data FROM a table different FROM the one in the outer query?

A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.

Can subquery retrieve data FROM inner query used in WHERE clause?

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.


1 Answers

Put an alias name in the main query

SELECT 
   tcase.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 As tcase                       <=====

And use this alias name in the subquery:

 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 = tcase.id
 FOR XML PATH('')
 ),1,2,'')
 AS ConcatGroupShares

More about co-related sub-queries:

http://en.wikipedia.org/wiki/Correlated_subquery

like image 118
cosmos Avatar answered Oct 14 '22 19:10

cosmos