连接两个表并显示所有记录 [英] join two tables and show all records

问题描述

我在 Microsoft Access 2013 中有两个表:

tblService

<前>PART_ID SERV_DATE SERV_REMARKA0001 11/1/2013 好#1A0001 11/13/2013 好#2A0001 11/25/2013 好#3

tblWithdraw

<前>PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TOA0001 11/6/2013 PM OW601A0001 11/20/2013 下午 120 点 OW603A0001 11/30/2013 更换 OW605

这是我想要的结果:

<前>PART_ID SERV_DATE SERV_REMARK DRAWN_DATE DRAWN_REASON DRAWN_TOA0001 11/1/2013 好#1 11/6/2013 PM OW601A0001 11/13/2013 好#2 11/20/2013 120 PM OW603A0001 11/25/2013 好#3 11/30/2013 更换 OW605

但是我既不能用INNER JOIN、RIGHT JOIN 也不能用LEFT JOIN 得到结果.我尝试使用 FULL JOIN，但系统提示我错误:

<块引用>

FROM 子句中的语法错误

下面是我的 SQL.

SELECTS.PART_ID、SERV_DATE、SERV_REMARK，DRAWN_DATE，DRAWN_REASON，DRAWN_TO从表服务AS右加入tblWithdraw AS W上S.PART_ID=W.PART_ID;

将结果显示为:

<前>PART_ID SERV_DATE SERV_REMARK DRAWN_DATE DRAWN_REASON DRAWN_TOA0001 11/1/2013 好#1 11/6/2013 PM OW601A0001 11/13/2013 好#2 11/6/2013 PM OW601A0001 11/25/2013 好#3 11/6/2013 PM OW601A0001 11/1/2013 好#1 11/20/2013 120 PM OW603A0001 11/13/2013 好#2 11/20/2013 120 PM OW603A0001 11/25/2013 好#3 11/20/2013 120 PM OW603A0001 11/1/2013 好#1 11/30/2013 更换 OW605A0001 11/13/2013 好#2 11/30/2013 更换 OW605A0001 11/25/2013 好#3 11/30/2013 更换 OW605

有人可以纠正我的 SQL 吗?或者任何指向正确代码的链接?谢谢！

解决方案

您的两个表之间没有唯一的关系，而您需要一个.例如，您将不得不重构您的表格(以及如何将数据输入到表格中)，以便它们具有一个值，使零件的每个实例与其他具有相同 PART_ID 的实例不同.

例如:

UID PART_ID SERV_DATE SERV_REMARK1 A0001 11/1/2013 好#12 A0001 11/13/2013 好#23 A0001 11/25/2013 好#3UID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO1 A0001 11/6/2013 下午 OW6012 A0001 2013 年 11 月 20 日下午 120 点 OW6033 A0001 11/30/2013 更换 OW605

然后你可以将你的 SQL 修改为:

SELECTS.PART_ID、SERV_DATE、SERV_REMARK，DRAWN_DATE，DRAWN_REASON, DRAWN_TO从表服务AS右加入tblWithdraw AS W上S.UID=W.UID;

这将给出您正在寻找的结果.

I have two tables in Microsoft Access 2013:

tblService

PART_ID  SERV_DATE   SERV_REMARK  
A0001    11/1/2013   GOOD#1  
A0001    11/13/2013  GOOD#2  
A0001    11/25/2013  GOOD#3

tblWithdraw

PART_ID DRAWN_DATE  DRAWN_REASON  DRAWN_TO  
A0001   11/6/2013   PM            OW601  
A0001   11/20/2013  120 PM        OW603  
A0001   11/30/2013  REPLACEMENT   OW605  

This is the result I want:

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  

But I can get the result neither with INNER JOIN, RIGHT JOIN nor LEFT JOIN. I tried with FULL JOIN, but system prompt me error:

Syntax error in FROM clause

Below is my SQL.

SELECT 
  S.PART_ID, SERV_DATE, 
  SERV_REMARK, DRAWN_DATE, 
  DRAWN_REASON, DRAWN_TO
FROM 
  tblService AS S 
RIGHT JOIN 
  tblWithdraw AS W 
ON 
  S.PART_ID=W.PART_ID;

which shows the result as:

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/6/2013        PM         OW601  
 A0001  11/25/2013    GOOD#3     11/6/2013        PM         OW601
 A0001  11/1/2013     GOOD#1     11/20/2013     120 PM       OW603  
 A0001  11/13/2013    GOOD#2     11/20/2013     120 PM       OW603  
 A0001  11/25/2013    GOOD#3     11/20/2013     120 PM       OW603
 A0001  11/1/2013     GOOD#1     11/30/2013  REPLACEMENT     OW605  
 A0001  11/13/2013    GOOD#2     11/30/2013  REPLACEMENT     OW605  
 A0001  11/25/2013    GOOD#3     11/30/2013  REPLACEMENT     OW605

Can somebody correct my SQL? Or any link to the right code? Thx!

解决方案

You don't have a unique relationship between your two tables and you need one. You will have to restructure your tables (and how data is entered in to them), for example, so that they have a value that makes each instance of a part individual from others with the same PART_ID.

For example:

UID  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

UID  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

Then you can modify your SQL to:

SELECT 
  S.PART_ID, SERV_DATE, 
  SERV_REMARK, DRAWN_DATE, 
  DRAWN_REASON, DRAWN_TO
FROM 
  tblService AS S 
RIGHT JOIN 
  tblWithdraw AS W 
ON 
  S.UID=W.UID;

This would give the result you are looking for.

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