As a result of OUTER JOIN, there are no rows, no WHERE clause (Workaround found)

Question

Update below.

I'm trying to make my own external join, which for each record returns it and all other records that appear later, or NULL if it is the last record itself. 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 problem is that when for a given [CR #], A.REGIS_STATUSDATE is maximum (and therefore B.REGIS_STATUSDATE cannot be greater than this), this line 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 the following:

 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 don't have a WHERE clause, I expect all the rows from my source table to be the result, but that is not the case. What am I missing here?

Edit: this is in Access 2007

Update. I decided to see what happens if I copy the sections of the CR_ADMIN_REGIS_STATUS table into a separate table and run my query. Even when I just copied the whole table to a new one (manually), the query works! It was only in the case when we actually copied and pasted, when I would select * In another table the problem would remain. In the end, I found that if I ran the query with

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

and not CR_ADMIN_REGIS_STATUS my request itself returned the desired result. Weird I also had a similar query to a similar table that worked from the very beginning, so it seems like it was a problem limited to this single table.

Answer 1

You are missing nothing. If this happens, this is a mistake.

There are several errors in the engine used by MS-Access. I saw a similar, inavlid behavior in joins that had “complex” ON conditions. See Another SO question in which 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 using UNION , but you can never be sure of 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 ) ;