al4629740
asked on
LEFT JOIN when using CROSS APPLY
I have an SQL query which is delivering a LEFT JOIN list of groups from my tblOrgProfile table.
I want the same LEFT JOIN list of groups to pop up in the following query, but am unable. Is there a way to LEFT JOIN the tblOrgProfile table in the second query below?
DECLARE @fromdt AS datetime
DECLARE @todt AS datetime
SET @fromdt = '12/1/2014'
SET @todt = '12/31/2014'
SELECT p.Agency, count(distinct h.RegID)
FROM tblOrgProfile as p
LEFT JOIN tblOrgRegistrations as r
ON p.AgencyID = r.AgencyID
AND r.Volunteer = 1
LEFT JOIN tblOrgHours as h
ON h.RegID = r.regid
AND h.ActivityDate >= @fromdt
AND h.ActivityDate <= @todt
AND h.Hours > 0
AND h.VolunteerHour = 1
WHERE p.CSWGroup = 1
GROUP BY p.Agency
ORDER BY p.Agency
I want the same LEFT JOIN list of groups to pop up in the following query, but am unable. Is there a way to LEFT JOIN the tblOrgProfile table in the second query below?
SELECT P.Agency, CA.LinkageName FROM tblOrgProfile P CROSS APPLY (SELECT
STUFF((SELECT '; ' + L.LinkageName
FROM tblOrgMonthlyReport M
JOIN tblOrgMonthlyLinkages L ON M.MonthlyID = L.MonthlyID
Where P.AgencyID = m.AgencyID
FOR XML PATH (''))
, 1, 1, '')
, STUFF((SELECT '; ' + L.Linkage
FROM tblOrgMonthlyReport M
JOIN tblOrgMonthlyLinkages L ON M.MonthlyID = L.MonthlyID
Where P.AgencyID = m.AgencyID
FOR XML PATH ('')), 1, 1, '')) CA (LinkageName, Linkage) where P.CSWGroup = 1
Try OUTER APPLY in place of CROSS APPLY
ASKER
That DID NOT work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
al4629740, you still have the issue or it's already solved?
ASKER
still having issues and trying to post a follow up
ASKER
Thank you