交叉连接，然后左连接

发布于 2024-09-09 02:21:31 字数 636 浏览 11 评论 0原文

是否可以在两个表之间进行交叉联接，然后对第三个表进行左联接，然后可能进行更多左联接？我正在使用 SQL Server 2000/2005。

我正在运行以下查询，这在我看来非常简单，但我收到错误。

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” 无法绑定。

如果我删除 LEFT JOIN，查询将有效。但是，我需要 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 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只是我以为 2024-09-16 02:21:31

您在查询中忘记了 CROSS JOIN：

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

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

回复收藏 0 原文

℡寂寞咖啡 2024-09-16 02:21:31

您无法组合隐式连接和显式连接 - 请参阅此运行示例。

交叉连接在系统中应该很少使用，因此我希望每个连接都明确，以确保它显然不是编码错误或设计错误。

如果要执行隐式左外连接，请执行以下操作（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

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

回复收藏 0 原文

~没有更多了~