Cross Join followed by Left Join

Question

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 the following query, which is pretty straightForward IMO, but I am getting an error.

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from Period P, Member M

LEFT JOIN Audits A 
ON P.PeriodId = A.PeriodId

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

I am getting the following error:

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "P.PeriodId" could not be bound.

If I remove the LEFT JOIN, the query works. However, I need the LEFT JOIN, as there is more information that I need to pull from other tables.

What am I doing wrong? Is there a better way to this?

Answer 1

you forgot CROSS JOIN in your query:

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from Period P CROSS JOIN Member M

LEFT JOIN Audits A 
ON P.PeriodId = A.PeriodId

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

Answer 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 > 29 AND P.PeriodID < 38
    AND M.AuditTypeId in (1,2,3,4)
    AND P.PeriodId *= A.PeriodId
order by M.Name​