合并MS Access 2003中的三个表 [英] Combine three tables in MS Access 2003

问题描述

我在Access 2003中有三个查询，需要将它们合并到一个查询中.并非所有的记录都存在于所有三个表中，所以我想我想要一个像完整的外部联接之类的东西.

I have three queries in Access 2003 which I need to combine in the one query. Not all records exist on all three tables so I suppose I want something like a full outer join.

查询1个字段: Record number, surname, firstname, subcentre

查询2个字段: Record number, surname, firstname, case worker

查询3个字段: Record number, surname, firstname, doctor

我需要能够显示:

Record number, surname, firstname, case worker, doctor, subcentre

但是，此刻我可以锻炼如何在两个查询之间使用左联接然后与右联接合并，但是不知道如何将其扩展到三个查询.

However, at the moment I can workout how to use a left join then union with a right join between two queries but don't know how to extend this to three queries.

有什么想法/建议吗?

推荐答案

如何:

SELECT a.[Record number], a.surname, a.firstname, 
       t1.subcentre, t2.[case worker], t3.doctor
FROM
(((SELECT [Record number], surname, firstname FROM T1
UNION
SELECT [Record number], surname, firstname FROM T2
UNION
SELECT [Record number], surname, firstname FROM T3) As A
LEFT JOIN T1 ON A.[Record number]=T1.[Record number])
LEFT JOIN T2 ON A.[Record number]=T2.[Record number])
LEFT JOIN T3 ON A.[Record number]=T3.[Record number]

在查询中使用UNION而不是UNION ALL会创建一个从所有三个表中提取的[Record number], surname, first name的唯一列表.然后，可以使用派生表将LEFT JOIN联接到其他三个表. LEFT JOIN确保包含派生表中的所有记录.

The use of UNION rather than UNION ALL in the query creates a unique list of [Record number], surname, first name taken from all three tables. The derived table can then be used to LEFT JOIN to the other three tables. LEFT JOIN ensures that all records from the derived tables are included.

与SQL一样，还有其他类似的方法可以执行此操作.在很大的文件上，上述操作会很慢，但是除非您使用的不是Jet/ACE后端，否则您不太可能会注意到任何延迟.

As usual with SQL, there are other, similar ways to do this. The above would be slow on a very large file, but unless you have something other than a Jet/ACE back-end, it is unlikely that you will notice any delay.

例如，对于SQL Server后端，您将使用传递查询和t-sql语法.对于MySQL后端，我认为您必须在一个UNION语句上方使用传递查询.

With, say, an SQL Server back-end, you would use a pass-through query and t-sql syntax. With MySQL back-end, I think you are obliged to use a passthrough query when you go above the one UNION statement.

这篇关于合并MS Access 2003中的三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！