Sql左连接和内连接 [英] Sql left join and inner join

问题描述

这是我的查询的SQL（仅限链接部分）。令人沮丧的是，我成功地将其联系起来而没有保存就退出了。这是一个周末和2个工作日，我无法弄清楚正确的链接顺序。我的左表（首先列出）VTG_COPO_HIST包含我自己需要的所有行。按订单号链接到第二个表时，所有必需的行都在那里。但是，我需要通过部件号进一步链接以提取部件号特定的字段点。当我使用内部联接按部件号链接时，我丢失了一行，因为我的VTG表中的特定行对于该字段为空。所以我将部分#level的连接更改为Left Join。丢失的行仍然没有进入。我的猜测是我的代码中有一些矛盾，以防止它包含该空行。

Here is the SQL to my query (the link portion only). What is frustrating is I had linked this successfully and exited without saving. It has been a weekend and 2 working days and I have not been able to figure out the correct linking order. My left table (listed first after from) VTG_COPO_HIST contains all of the lines I need on it's own. When linked to the second table by order number, all of the required lines are there. However, I will need to link further by part number to pull in part number specific field points. When I link by part number with an inner join, I lose one row as that particular row in my VTG table is null for that field. So I change the join of the part # level to Left Join. The missing row still does not pull in. My guess is that I have something contradictory in my code to prevent it from including that null line.

FROM {oj VMFG.dbo.VTG_COPO_HIST LEFT OUTER JOIN VMFG.dbo.INVENTORY_TRANS ON VTG_COPO_HIST.PART_ID_NEW = INVENTORY_TRANS.PART_ID}
WHERE VTG_COPO_HIST.ORDER_ID = INVENTORY_TRANS.CUST_ORDER_ID AND ((VTG_COPO_HIST.ORDER_ID='h111212'))

我尝试过：



我已经写了一个可能的订单和内部/外部连接组合的列表，并尝试了每种可能的组合。我在谷歌搜索并在论坛中看到它往往有一个矛盾的where声明，如果我有，我找不到。我对VBA感觉更舒服，SQL对我来说是全新的。

What I have tried:

I have written a list of possible orders and combos of inner/outer joins and tried every possible combination. I have googled and seen in forums that it tends to be having a contradictory "where" statement, which if I have, I cannot find. I am much more comfortable with VBA, SQL is completely new for me.

推荐答案

无法看到你的表，我可以给你设计的如何您的联接以及您将（或不会）消除行的原因。


到目前为止，您已经发现第三个表的内部联接将如何消除某些行并且正确地意识到你需要一个左连接来维护主表中的所有基本行。



左连接将显示顶部的所有选定行 - 级别表以及JOINed表中的值或NULL。现在，在添加第三个表时，您需要关心连接的ON组件。让我们假设您的第三个表正在第二个表中查找值。如果使用INNER JOIN，无论它何时失败，它都将删除整行，因为它消除了第二个表值。如果使用LEFT JOIN，则将值与要找到链接的表匹配。您需要将ON定位到正确的表格。



您的精简版本（SELECT部分​​在创建更具体的答案时非常有帮助）。逻辑布局可以是HISTORY-> ORDER-> PART。



因此，重新表达：您请求您的首选客户列表并获得这些客户的订单然后每个订单的组件。至少我就是这样做的。



但是，如前面的评论中所述，你没有给出表格架构，我们还不够信息。

Not being able to see your table, I can give you the "How" for designing your joins and a why you will (or will not) eliminate rows.

So far, you have discovered how an inner join with the third table will wipe out some rows and correctly realized that you need a left-join to maintain all of the base rows from your primary table.

A Left join will show all selected rows in the top-level table and either a value or NULL for those in the JOINed table. Now, when adding the third table, you need to be care about the ON component of the join. Let's presume that your third table is looking for values in the second table. If you use an INNER JOIN, wherever it fails it will remove the entire row because it's eliminating the second table value. If you use a LEFT JOIN, and match the value to the table where the linkage is to found. You need to target that ON to the correct table.

Your stripped down version (the SELECT portion would have been very helpful in creating a more specific answer). A logical layout would be HISTORY->ORDER->PART.

So, rehashing: You request your preferred customer list and get orders for those customers and then the components for each order. At least that's how I'd lay it out.

But, as noted in the comment preceding this, you do not give the table schema and we've not enough information.

看起来像where子句中的问题

Looks like the issue in where clause

这篇关于Sql左连接和内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！