Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

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.

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

Open in new window


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

Open in new window

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Try OUTER APPLY in place of CROSS APPLY
Avatar of al4629740

ASKER

That DID NOT work.
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
al4629740, you still have the issue or it's already solved?
still having issues and trying to post a follow up
Thank you