Microsoft SQL Server
Microsoft SQL Server

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: 2015-02-25

Who is Participating?

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-02-25

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.

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-02-25

Try OUTER APPLY in place of CROSS APPLY

al4629740Author Commented: 2015-02-25

That DID NOT work.

LVL 50

PortletPaulfreelancerCommented: 2015-02-25

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
;