Inaccurate results from Outer Join query on linked tables 1

[AceFM]

I have run into a very strange problem. Running MS Access 2003, SP3.

I am running a simple query with 2 tables, using an outer join, looking for a list of all records in table 1 where there is no corresponding entry in table 2:

SELECT Table1.key,
Table1.lastname,
Table1.firstname
FROM Table1 LEFT JOIN Table2
ON Table1.key = Table2.Table1key
WHERE (((Table2.key) Is Null));

When I run this query against imported copies of the tables, it works great. The problem is, when I run the exact same query against these tables linked instead of imported, the query does not work. It returns far too much data.

In particular, I recently added rows to Table2 so now there should always be at least one row in table2 linked to table1. The query correctly returns no data when run against the imported tables. It incorrectly returns 35,000+ rows when run against the linked tables.

Any thoughts would be welcome. I have searched through the microsoft helpdesk and online, but cannot find this particular issue as a known bug. I did find one issue with linked outer joins, stating there could be an issue in the Jet driver, but my files are all up-to-date, so I do not believe that is the problem.

Thanks

 

[CaptainD]

Why not

SELECT Table1.key,
 Table1.lastname,
 Table1.firstname
FROM Table1
WHERE Table1.key NOT IN(SELECT Table2.Key from Table2)

 

[AceFM]

Using the subquery works properly. I appreciate the suggestion.

The inaccurate results from the first query is still a big problem here because that is the code generated by Access in the Design view. And there are several people here who are tasked with running queries who are new to SQL and rely on that design view. They cannot just jump to the SQL view and re-type the query.

So, until we find a solution for the problem, all queries on linked tables generated in the design view are suspect. That is causing major issues here.

 

[cmmrfrds]

What is the backend database you are linking to?

Are key1 and key2 the actual primary keys in the linked tables?

 

[AceFM]

Linking to an Oracle Sybase db via ODBC.

And on the second question, Yes.
Primary key for Table1 is Key1
Primary key for Table2 is Key2
Table2 stores Key1 in a separate field to link to Table1 in a Mto1 relationship.

 

[CaptainD]

This is a SWAG but try adding Table2.Key to see what it is returning.


SELECT Table1.key,
Table1.lastname,
Table1.firstname,
Table2.Key
FROM Table1 LEFT JOIN Table2
ON Table1.key = Table2.Table1key
WHERE (((Table2.key) Is Null));

 

[cmmrfrds]

SELECT Table1.key,
Table1.lastname,
Table1.firstname
FROM Table1 LEFT JOIN Table2
ON Table1.key = Table2.Table1key
WHERE (((Table2.key) Is Null));

If table2.key is actually the foreign key to table 1 and is not the primary key to table 2, then it could be null regardless of the left join. Make sure the is null test is testing the primary key on table 2, not the foreign key.

 

[CaptainD]

cnmrfrds has a point. If there is / are null values in table2.table1key then the query might be linking on the null.

Try a test

SELECT * FROM Table2 WHERE Table2.Table1Key Is Null

 

[AceFM]

Thanks for the recommendations. I am new to this forum, and it is great to see how responsive people are.

Unfortunately, the idea suggested by cmmrfrds is not going to help. Table2.key2 is the primary key for table2, so it always exists if there is a row in Table2. Sorry if I did not make that clear.

And when I search for [Table2.Table1Key is null], I get no returns, so there are no rows in Table2 with empty Table1Key values, which is expected, as that is a required field.

The other point that I want to highlight, which is the one that really screams at me that there is a deeper problem, is that when I import these tables locally, instead of linking to them, the query works just fine.

I run the query on the imported tables - no data returned, which is what I am expecting - that shows that all rows in Table1 have at least 1 row in Table2 with the Table1Key.

However, the query on linked tables returns a bunch of rows from Table1 as if they have no corresponding Table2 entries. But I can go back into the Table2 and find rows that have the Table1Key, so the join is not working properly.

I appreciate your help. This one has me stumped.

 

[cmmrfrds]

I would like to clarify one thing. When you say that the backend database(s) is "Oracle Sybase db". Does that mean 1 table is on Oracle and the other on Sybase?

 

[AceFM]

Sorry for the confusion. I should proofread a little better.

The database is Sybase for both tables.

 

[cmmrfrds]

Have you tried doing a pass-thru query. In other words, taking the query you have and saving it as a pass-thru query and running it that way. This way it will run directly on Sybase and eliminate the Sybase tables as the problem area. Can you check this out next?

 

[AceFM]

That worked!

Very interesting.

I run the query against the linked tables, and the results are incorrect. But when I run the pass-through query, it does work.

It had been my understanding that setting up linked tables is supposed to accomplish the same basic thing as running the pass-through query.

Have you seen this before? Or any recommendations on how to get my linked tables to process the query correctly? I don't think just deleting the link and re-establishing it will help, as I have done that before.

 

[cmmrfrds]

I believe, what this shows is that Sybase is handling the query correctly and therefore the syntax of the SQL is correct. It points the problem to ODBC and the Jet Engine interface through ODBC to the Sybase data. When using the linked tables, information from both tables will be copied to Access so that the Jet Engine can do the query on the client side. There must be some break down in the process. I would start by looking at the join keys in the Access linked table and see whether the data types exactly match. Maybe you could try another, later version, of the ODBC driver. Thats about all I can offer at this point.

 

[AceFM]

Thanks. I had checked the Jet driver version earlier in the process, and it looked correct. But I'll go back and double-check that. At least now I know that is the problem area that needs to be focused on.

 

[AceFM]

Any ideas where to get a driver for the Sybase 12 ODBC? I have just wasted a lot of time trying to get information on Sybase drivers. It has been an exercise in frustration.

 

[cmmrfrds]

I don't use Sybase, so I'm not sure where to get their ODBC drivers.

I suppose for a test you could download a trial copy from DataDirect to see if it acts differently.

http://www.datadirect.com/products/odbc/odbc-sybase/index.ssp