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

al4629740Asked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
There may be some other issues with your query. Here is a working sample which might help to troubleshoot your query:
SELECT S.supplierid, S.companyname AS supplier, A.*
FROM Production.Suppliers AS S
OUTER APPLY (SELECT productid, productname, unitprice
FROM Production.Products AS P
WHERE P.supplierid = S.supplierid
ORDER BY unitprice, productid
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A
WHERE S.country = N'Japan';
supplierid supplier             productid productname unitprice
4        Supplier QOVFD	74        Product BKAZJ	       10.00
4        Supplier QOVFD	10        Product YHXGE	       31.00
6        Supplier QWUSF	13        Product POXFU        6.00
6        Supplier QWUSF	15        Product KSZOI	       15.50
30       Supplier XYZ   NULL      NULL                 NULL
 ^-- supplier 30 was preserved even though it has no related products. 

The OUTER APPLY operator does what the CROSS APPLY operator does, but also includes in the result rows from the left side that get an empty set back from the right side. NULLs are used as placeholders for the result columns from the right side. In other words, the OUTER APPLY operator preserves the left side. In a sense, the difference between OUTER APPLY and CROSS APPLY is similar to the difference between a LEFT OUTER JOIN and an INNER JOIN. 

Open in new window


At first, consider simplifying your query a bit to get the structure right in respect to left join behavior and then add to it all fields you need to have.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Try OUTER APPLY in place of CROSS APPLY
0
 
al4629740Author Commented:
That DID NOT work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
perhaps using a derived table covering the cross apply, then left join the other tables.

SELECT
      P.Agency
    , P.LinkageName
    -- more columns
FROM (
      SELECT
            * --<< should list the fields needed 
      FROM tblOrgProfile 
      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)
      ) 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
;

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
al4629740, you still have the issue or it's already solved?
0
 
al4629740Author Commented:
still having issues and trying to post a follow up
0
 
al4629740Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.