交叉联接，然后是左联接 [英] Cross Join followed by Left Join

问题描述

是否可以在2个表之间进行CROSS JOIN，然后在第3个表上进行LEFT JOIN，然后再进行更多的左联接?我正在使用SQL Server 2000/2005.

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.

我正在运行以下查询，这是非常简单的IMO，但出现错误.

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

我遇到以下错误:

信息4104，第16级，状态1，第1行 多部分标识符"P.PeriodId" 无法绑定.

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

如果删除LEFT JOIN，则查询有效.但是，我需要LEFT JOIN，因为我需要从其他表中提取更多信息.

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?

推荐答案

您不能结合使用隐式和显式联接-

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

Cross JOINs在系统中应该很少使用，我希望每个人都明确表示，以确保这显然不是编码错误或设计错误.

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.

如果要执行隐式左外部联接，请执行此操作(SQL Azure不支持):

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​

这篇关于交叉联接，然后是左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！