OUTER JOIN result is missing rows, no WHERE clause (Workaround found)

Question

Update at the bottom.

I am trying to do a self outer join that, for each record, returns it and all other records occuring later than it, or NULL if it itself is the latest record. This is my sql code:

SELECT A.[CR#], A.REGIS_STATUSDATE, B.REGIS_STATUSDATE
FROM CR_ADMIN_REGIS_STATUS A LEFT OUTER JOIN CR_ADMIN_REGIS_STATUS B
ON A.[CR#]=B.[CR#] AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE

My issue is this is that when, for a given [CR#], A.REGIS_STATUSDATE is a maximum (and therefore the B.REGIS_STATUSDATE cannot be greater than it), that row is not included in my result.

For example, if CR_ADMIN_REGIS_STATUS looks like this:

CR#   REGIS_STATUSDATE
1     5/1/12
1     5/2/12
1     5/3/12
2     5/1/12
2     5/2/12

I expect the result of my query to be

CR#   A.REGIS_STATUSDATE B.REGIS_STATUSDATE
1     5/1/12             5/2/12
1     5/1/12             5/3/12
1     5/2/12             5/3/12
1     5/3/12             NULL
2     5/1/12             5/2/12
2     5/2/12             NULL

Instead I get this:

CR#   A.REGIS_STATUSDATE B.REGIS_STATUSDATE
1     5/1/12             5/2/12
1     5/1/12             5/3/12
1     5/2/12             5/3/12
2     5/1/12             5/2/12

Given that my query is a LEFT OUTER JOIN, and I have no WHERE clause, I expect all rows from my original table to be in the result, but this is not the case. What am I missing here?

Edit: This is in Access 2007

Update: I decided to see what would happen if I copied sections of the table CR_ADMIN_REGIS_STATUS into a separate table and ran my query against that. Even when I had just straight up copied the entire table into the new one (manually) the query worked! This was only the case when actually copying and pasting though, when I would SELECT * INTO another table the problem would persist.
Eventually I discovered that if I ran the query against

SELECT *
FROM CR_ADMIN_REGIS_STATUS
UNION ALL SELECT TOP 1 * 
FROM CR_ADMIN_REGIS_STATUS;

rather than CR_ADMIN_REGIS_STATUS itself my query returned the desired result. Weird. I also had a similar query against a similar table which worked from the start, so it seems this was an issue limited to this one table.

Traveller

Well thats got me scratching my head as well – Onanism

In MS Access 2010 running your query I get your expected result. – Shamrock

Answer 1

You are not missing anything. If this happens, it's a bug.

The engine used by MS-Access has several bugs. I've seen similar, inavlid behaviour in joins that had "complex" ON conditions. See another SO question where Access gives buggy results: Why does my left join in Access have fewer rows than the left table?

You can try the query with identical data in SQL-Server, Oracle, Postgres, even MySQL and you will get the correct, expected results.

---

As a workaround, you can try rewriting the query with a UNION, but one can never be sure about the correctness:

SELECT A.[CR#], A.REGIS_STATUSDATE, B.REGIS_STATUSDATE
FROM CR_ADMIN_REGIS_STATUS A 
  INNER JOIN CR_ADMIN_REGIS_STATUS B
    ON  A.[CR#]=B.[CR#] 
    AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE

UNION ALL

SELECT A.[CR#], A.REGIS_STATUSDATE, NULL
FROM CR_ADMIN_REGIS_STATUS A 
WHERE NOT EXISTS
      ( SELECT *
        FROM CR_ADMIN_REGIS_STATUS B
        WHERE A.[CR#]=B.[CR#] 
          AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE
      ) ;

Chartres

I get the expected result in MS Access. – Shamrock

Thank you for your quick answer, as disappointing as it is to hear about that bug. I will try your workaround. – Traveller

@Remou: When I answered that other question, I had made a few tests, with 2 tables and very few rows, 2 or 3 each. Queries that had ON a.id=b.id OR a.x IS NULL or similar conditions yielded very strange results. – Hardecanute

@Remou: I had tested in 2007 version, too. But this condition seems more normal and common than the other one. I was rather surprised by that bug but this is even more surprising as there are no Nulls involved. – Hardecanute