带有WHERE子句的LEFT OUTER JOIN [英] LEFT OUTER JOIN with a WHERE clause

问题描述

我有两个桌子. indRailType包含与ID值配对的名称列表，我在其他表中使用该ID值来指示导轨类型. WO_BreakerRail包含一个日期列和一个铁路代码列，它们与indRailType中的相同代码和一些其他数据相对应. WO_BreakerRail中有一行，用于每种日期的每种滑轨类型上的任何活动.因此，我可以有3行标明3/19/2010的行，每行表示不同的铁路代码，以及发生了什么.

I have two tables. indRailType contains a list of the names paired with an ID value that I use in other tables to indicate the rail type. WO_BreakerRail contains a date column and a rail code column that corresponds to the same code in indRailType and some other data. There's a row in WO_BreakerRail for any activity on each rail type, for every date. So I could have 3 rows dated for 3/19/2010, each row indicates a different rail code, and what happened.

当我使用以下LEFT OUTER JOIN时，我得到了一个表，其中包含所有类型的rail，在19号未发生任何行为的行中为空.现在，这仅行得通，因为我的WO_BreakerRail表中现在只有一个日期，即19号.当我添加更多具有不同日期的行时，事情就会变得一团糟.

When I use the following LEFT OUTER JOIN, I get a table with all the types of rail, with nulls in the rows where nothing happened on the 19th. Now, this is only working because I only have one date represented in my WO_BreakerRail table right now, the 19th. When I add more rows with different dates, things will go haywire.

这是我的SQL语句，现在可以准确地给出我想要的结果:

This is my SQL statement, which right now gives me exactly the results I want:

SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces, 
    WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged, 
    WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
FROM indRailType
LEFT OUTER JOIN WO_BreakerRail 
    ON indRailType.RailCode = WO_BreakerRail.RailCode

现在，当我添加WHERE WO_BreakerRail.Date = @Date子句时，我丢失了JOIN中的所有行，但没有任何反应.我不要通过阅读，听起来像是我要的是OUTER JOIN，但是SQL Server Compact Edition不支持FULL OUTER JOIN.有没有解决的办法，还是我正在寻找其他东西?

Now, when I add in a WHERE WO_BreakerRail.Date = @Date clause I lose all the rows in the JOIN which nothing happened. I don't want that. From reading up, it sounds like a FULL OUTER JOIN is what I want, but SQL Server Compact Edition doesn't support FULL OUTER JOINs. Is there a way around this, or am I looking for something else entirely?

推荐答案

尝试:

SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces, 
    WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged,
    WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
FROM indRailType
LEFT OUTER JOIN WO_BreakerRail ON indRailType.RailCode = WO_BreakerRail.RailCode 
            AND WO_BreakerRail.Date = @Date

因此将AND WO_BreakerRail.Date = @Date添加到联接中

这篇关于带有WHERE子句的LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！