Why does my left member in Access have fewer rows than the left table?

Question

I have two tables in the 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 record in both tables. Therefore, I am making a join between the two tables of names and dates of birth to see which records correspond. I use the left join to get strings for people who are in TblIndividualsUpdates but not in TBLIndividuals. This way, I know which entries need to be added to TBLIndividuals in order to update it.

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 has 4149 rows, but the query returns only 4103 rows. TblIndividualsUpdates has about 50 new entries, but only 4 lines from the query, where OldID is null.

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

Is this a bug in Access? Is there a difference between access semantics on the left and PostgreSQL? Is my database damaged (Compact and Repair do not help)?

Answer 1

It should never be. If the rows are not inserted / deleted, however,

inquiry:

 SELECT * FROM a LEFT JOIN b ON whatever ; 

should never return fewer rows than:

 SELECT * FROM a ; 

If this happens, this is a mistake . Are you sure the queries are exactly like this (and you did not specify some details like the WHERE )? Are you sure that the first returns 4149 lines, and the second - 4103 lines? You can do another check by changing * above to COUNT(*) .

Answer 2

 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 ) ) ) ); 

What would I do, systematically delete all the connection conditions except the first two, until you find that the records drop out. Then you will find out where your problem is.

Answer 3

Drop any indexes from both tables that include these JOIN fields (FirstName, LastName, and DateBorn). Then see if you get the expected 4,149 rows with this simplified query.

 SELECT i.PersonID AS OldID, u.PersonID AS UpdateID FROM TblIndividualsUpdates AS u LEFT JOIN TblIndividuals AS i ON ( (i.FirstName = u.FirstName) AND (i.LastName = u.LastName) AND (i.DateBorn = u.DateBorn) ); 

Answer 4

For whatever it costs, as it seems to be a deceptive mistake, and any additional information can help solve it, I had the same problem.

The request is too large to publish here, and I don’t have time to shorten it now to something suitable, but I can tell you what I found. In the example below, all connections are left connections.

I gradually clarified and changed my request. It has a view (D). And all this was done in a view (T), and then joined to the last table (L). In any case, at some point in its development, not a single field in T that arose in D participated in the union with L. Then a problem arose, the total number of rows mysteriously became less than the main table, which should be impossible, As soon as I will again allow the field from D to participate (via T) in conjunction with L, the number again increased to normal.

It was as if the condition for joining D was transferred to the WHERE clause when no field (through T) in conjunction with L. participated in it. But I really don't know what the explanation is.