外连接中的 AND 语法? [英] AND syntax in outer join?

问题描述

这是我发现有问题的更大查询的一小部分.

this is a small piece of a much larger query that I have discovered is a problem.

除了主键之外，我还尝试向外部联接添加过滤器.问题是我收到的结果的 StatusCode 不是 0 或 1，就好像它忽略了过滤器一样.

I'm trying to add a filter to my outer join in addition to the primary key. The problem is that I'm receiving results that have an StatusCode other than 0 or 1, as if it is ignoring the filter.

SELECT * 
FROM Products P
LEFT OUTER JOIN OrderDetails OD ON OD.SkuNum = P.SkuNum
LEFT OUTER JOIN OrderHeader OH ON (OD.ShipmentNum = OH.ShipmentNum
                               AND (OH.StatusCode = 0 OR OH.StatusCode = 1))
WHERE P.SkuNum = XXXX

请注意，如果我将该语句 (OH.StatusCode = 0 OR OH.StatusCode = 1) 放在 where 子句中，它会根据该条件过滤整个结果集，这也不是我想要的.

Note that if I put that statement (OH.StatusCode = 0 OR OH.StatusCode = 1) in the where clause it filters the entire result set by that criteria which is not what I want either.

对于这个简单的英语连接，我想说给我所有的产品和其他这里没有列出的东西.如果这个产品有任何发货，给我所有的详细信息货件状态为 1 或 0"

For this join in plain english, I'm trying to say "Give me all products and other stuff not listed here. If there are any shipments for this product, give me all the details for them where the shipment has a status of 1 or 0"

是我的语法错误还是遗漏了什么?谢谢.

Is my syntax wrong or am I missing something? Thanks.

更新了查询以包含产品，以便更清楚地了解我要查找的内容并修复了换位错误.

Updated the query to include products to make it clearer what I'm looking for and fixed a transposition error.

推荐答案

通常情况下，对于给定的 OrderDetail，您总会有一个 OrderHeader，但您可能没有状态为 0, 1 的 OrderHeader.

Typically you will always have an OrderHeader for a given OrderDetail, but you might not have one with status 0, 1.

通过进行左连接，您将获得该特定 Sku 的所有信息，然后如果订单标题没有状态 0、1，则这些列将为 NULL.

By doing a left join, you are getting all for that particular Sku, and then if the order header doesn't have status 0, 1, those columns will be NULL.

我认为您需要 INNER JOIN.但这通常等同于将所有内容都放在 WHERE 中，因此我确信您在此处完全描述了您想要获得的内容.

I would think you want an INNER JOIN. But then that would usually be equivalent to putting everything in the WHERE, so I'm note sure you're fully describing what you want to get here.

看到你的编辑后，试试这个:

After seeing your edit, try this:

SELECT * 
FROM Products P
LEFT JOIN (
    SELECT OD.SkuNum, OD.ShipmentNum, etc.
    FROM OrderDetails OD
    INNER JOIN OrderHeader OH
        ON OD.ShipmentNum = OH.ShipmentNum
            AND (OH.StatusCode = 0 OR OH.StatusCode = 1)
) AS Orders ON Orders.SkuNum = P.SkuNum
WHERE P.SkuNum = XXXX

这篇关于外连接中的 AND 语法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！