为什么我的左连接在Access中具有比左表少的行？ [英] Why does my left join in Access have fewer rows than the left table?

问题描述

我在MS Access 2010数据库中有两个表：TBLIndividuals和TblIndividualsUpdates。它们有很多相同的数据，但是主键可能不同于两个表中给定人的记录。所以我在名字和出生日期的两个表之间做一个联接，以查看哪些记录对应。我使用左连接，所以我也得到行的人谁在TblIndividualsUpdates，但不是在TBLIndividuals。这样我知道哪些记录需要添加到TBLIndividuals以获得它最新。

I have two tables in an MS Access 2010 database: TBLIndividuals and TblIndividualsUpdates. They have a lot of the same data, but the primary key may not be the same for a given person's record in both tables. So I'm doing a join between the two tables on names and birthdates to see which records correspond. I'm using a left join so that I also get rows for the people who are in TblIndividualsUpdates but not in TBLIndividuals. That way I know which records need to be added to TBLIndividuals to get it up to date.

SELECT TblIndividuals.PersonID AS OldID, 
TblIndividualsUpdates.PersonID AS UpdateID
FROM TblIndividualsUpdates LEFT JOIN TblIndividuals 
ON ( (TblIndividuals.FirstName = TblIndividualsUpdates.FirstName) 
and (TblIndividuals.LastName = TblIndividualsUpdates.LastName) 
AND (TblIndividuals.DateBorn = TblIndividualsUpdates.DateBorn 
    or (TblIndividuals.DateBorn is null 
        and (TblIndividuals.MidName is null and TblIndividualsUpdates.MidName is null 
            or TblIndividuals.MidName = TblIndividualsUpdates.MidName))));

TblIndividualsUpdates有4149行，但查询只返回4103行。

TblIndividualsUpdates has 4149 rows, but the query returns only 4103 rows. There are about 50 new records in TblIndividualsUpdates, but only 4 rows in the query result where OldID is null.

如果我将数据从Access导出到PostgreSQL并运行相同的方法，则在TblIndividualsUpdates中有大约50个新记录，但在查询结果中只有4行查询那里，我得到所有4149行。

If I export the data from Access to PostgreSQL and run the same query there, I get all 4149 rows.

这是Access中的错误吗？ Access的左连接语义和PostgreSQL之间有什么区别吗？我的数据库损坏（Compact和Repair不起作用）？

Is this a bug in Access? Is there a difference between Access's left join semantics and PostgreSQL's? Is my database corrupted (Compact and Repair doesn't help)?

推荐答案

除非在此期间插入/删除行，

This should never happen. Unless rows are being inserted/deleted in the meantime,

查询：

SELECT *
FROM a LEFT JOIN b
         ON whatever ;

不应返回少于的行：

SELECT *
FROM a ;

如果发生，这是一个错误。你确定查询是这样的（你没有省略一些细节，如 WHERE 子句）？您确定第一个返回4149行，第二个返回4103行？您可以通过将上述 * 更改为 COUNT（*）进行另一次检查。

If it happens, it's a bug. Are you sure the queries are exactly like this (and you have't omitted some detail, like a WHERE clause)? Are you sure that the first returns 4149 rows and the second one 4103 rows? You could make another check by changing the * above to COUNT(*).

这篇关于为什么我的左连接在Access中具有比左表少的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！