Cross Join Followed By Left Join

Is it possible to do a CROSS JOIN between 2 tables, followed by a LEFT JOIN on to a 3rd table, followed by possibly more left joins? I am using SQL Server 2000/2005. I am running t

Solution 1:

you forgot CROSS JOIN in your query:

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
fromPeriod P CROSSJOINMember M

LEFTJOIN Audits A 
ON P.PeriodId = A.PeriodId

WHERE 
    P.PeriodID >29AND P.PeriodID <38AND M.AuditTypeId in (1,2,3,4)
orderby M.Name

Solution 2:

You cannot combine implicit and explicit joins - see this running example.

CROSS JOINs should be so infrequently used in a system, that I would want every one to be explicit to ensure that it is clearly not a coding error or design mistake.

If you want to do an implicit left outer join, do this (not supported on SQL Azure):

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from #Period P, #Member M, #Audits A 
WHERE 
    P.PeriodID >29AND P.PeriodID <38AND M.AuditTypeId in (1,2,3,4)
    AND P.PeriodId *= A.PeriodId
orderby M.Name​