连接两个表并显示所有记录(包括空值) [英] Join two tables and show all records (including null)

问题描述

我在Microsoft Access 2013中有两个表:

I have two tables in Microsoft Access 2013:

ID PART_ID SERV_DATE SERV_REMARK
1   A0001  11/1/2013   GOOD#1
2   A0001  11/13/2013  GOOD#2
3   A0001  11/25/2013  GOOD#3
4   B0001  11/26/2013  GOOD#4
5   C0001  12/1/2013   GOOD#5
6   C0001  12/10/2013  GOOD#6
7   C0001  12/20/2013  GOOD#7

tblWithdraw

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO
1   A0001  11/6/2013       PM        OW601
2   A0001  11/20/2013    120 PM      OW603
3   A0001  11/30/2013  REPLACEMENT   OW605 
4   C0001  12/2/2013      30 PM      OW701
5   C0001  12/15/2013    180 PM      OW702

我想将两个表连接起来，结果如下所示:

I'd like to join the two tables to have the result as shown below:

PART_ID SERV_DATE SERV_REMARK DRAWN_DATE DRAWN_REASON DRAWN_TO
 A0001  11/1/2013   GOOD#1    11/6/2013       PM        OW601
 A0001  11/13/2013  GOOD#2    11/20/2013    120 PM      OW603
 A0001  11/25/2013  GOOD#3    11/30/2013  REPLACEMENT   OW605
 B0001  11/26/2013  GOOD#4
 C0001  12/1/2013   GOOD#5    12/2/2013       PM        OW701
 C0001  12/10/2013  GOOD#6    12/15/2013    180 PM      OW702
 C0001  12/20/2013  GOOD#7

我已经 工作了几周 ，但仍无法获得结果. 有人可以向我显示查询吗?非常感谢！

I have been working for weeks but still unable to get the result. Can somebody show me the Query? Thanks a lot!

我尝试过:

选择s.PART_ID，s.SERV_DATE，s.SERV_REMARK，
w.DRAWN_DATE，w.DRAWN_REASON，w.DRAWN_TO
来自tblService AS s
左加入tblWithdraw AS w
开启s.PART_ID = w.PART_ID

SELECT s.PART_ID, s.SERV_DATE, s.SERV_REMARK,
w.DRAWN_DATE, w.DRAWN_REASON, w.DRAWN_TO
FROM tblService AS s
LEFT JOIN tblWithdraw AS w
ON s.PART_ID = w.PART_ID

结果是:

PART_ID SERV_DATE   SERV_REMARK DRAWN_DATE  DRAWN_REASON    DRAWN_TO
 A0001  11/1/2013     GOOD #1   11/6/2013        PM           OW601
 A0001  11/1/2013     GOOD #1   11/20/2013     120 PM         OW602
 A0001  11/1/2013     GOOD #1   11/30/2013  REPLACEMENT       OW603
 A0001  11/13/2013    GOOD #2   11/6/2013        PM           OW601
 A0001  11/13/2013    GOOD #2   11/20/2013     120 PM         OW602
 A0001  11/13/2013    GOOD #2   11/30/2013  REPLACEMENT       OW603
 A0001  11/25/2013    GOOD #3   11/6/2013        PM           OW601
 A0001  11/25/2013    GOOD #3   11/20/2013     120 PM         OW602
 A0001  11/25/2013    GOOD #3   11/30/2013  REPLACEMENT       OW603
 B0001  11/26/2013    GOOD #4           
 C0001  12/1/2013     GOOD #5   12/2/2013        PM           OW701
 C0001  12/1/2013     GOOD #5   12/15/2013     180 PM         OW702
 C0001  12/10/2013    GOOD #6   12/2/2013        PM           OW701
 C0001  12/10/2013    GOOD #6   12/15/2013     180 PM         OW702
 C0001  12/20/2013    GOOD #7   12/2/2013        PM           OW701
 C0001  12/20/2013    GOOD #7   12/15/2013     180 PM         OW702

推荐答案

我本以为以下内容对您有用:

I originally thought that the following would work for you:

SELECT s.PART_ID, s.SERV_DATE, s.SERV_REMARK, 
  w.DRAWN_DATE, w.DRAWN_REASON, w.DRAWN_TO
FROM tblService AS s
  LEFT JOIN tblWithdraw AS w
  ON s.PART_ID = w.PART_ID

但是，由于在tblService表中部件ID不是唯一的，因此无法正常工作.因此，我开始寻找两个表中匹配的日期字段.日期字段不匹配.

However that would not work since part ID is not unique in the tblService table. So then I started looking for a date field that matched in the two tables. The date fields do not match.

因此，根据您在此处显示的内容，您无法将两个表链接在一起.

So, based on what you are showing here you have no way of linking the two tables together.

您的输出看起来像是要遍历tblService表，然后在tblWithdraw表中找到匹配的记录并将它们匹配在一起.然后，如果tblService表中存在第二个具有相同PART_ID的记录，则将其与tblWithdraw表中第二个具有相同PART_ID的记录匹配.然后，将其重复多次.如果tblWithdraw中没有匹配的记录与tblService中的第n个匹配项匹配，则无论如何都输出在tblService中找到的信息.

Your output looks like you want to iterate through the tblService table and then find a matching record in the tblWithdraw table and match them together. Then if there is a second record with the same PART_ID in the tblService table, match it with the second occurrence of the same PART_ID in the tblWithdraw table. Then repeat this as many times as it takes. If there isn't a matching record in tblWithdraw that matches the nth occurrence in tblService then output the information found in tblService anyway.

您无法在SQL中执行这种复杂的查询. 如果您确实想执行此操作，则可以使用VBA，C#和/或类似的代码进行迭代tblService，然后遍历tblWithdraw以查找所需内容.

You cannot perform such a convoluted query in SQL. If you really want to do this, you could use code like VBA, C# and/or etc to iterate through the tblService and then iterate through tblWithdraw to find what you are looking for.

一个更好的选择:如果显示的表仅包含要显示的字段，则需要修改表并将唯一的主键字段添加到tblService.然后在tblWithdraw表中添加与tblService的主键匹配的外键.

A better alternative: If the tables you are showing only contains the fields you are showing, then you need to modify the tables and add a unique primary key field to tblService. Then add a foreign key in the tblWithdraw table matching the primary key of tblService.

然后执行以下操作:

SELECT s.PART_ID, s.SERV_DATE, s.SERV_REMARK, 
  w.DRAWN_DATE, w.DRAWN_REASON, w.DRAWN_TO
FROM tblService AS s
  LEFT JOIN tblWithdraw AS w
  ON s.tblServiceID = w.tblServiceID 

这篇关于连接两个表并显示所有记录(包括空值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！