MS Access Inner Join doesn't recognize difference between small and capital letters?

Question

As title says, this issue happens in MS Access 2003 SP1. Does anyone know what could be solution for this problem?

Pseudo query select * from a inner join b on a.id=b.id

Answer 1

I believe that SQL in Access is not case sensitive. Try this Kb article for a possible solution.

Answer 2

Here's another possible solution. As mentioned in the posting, you are sacrificing speed...

Answer 3

Have you tried StrComp? Not an Access-bod, but I think the query would look something like this:

SELECT * FROM MyTable A INNER JOIN MyTable B 
on StrComp(A.description,B.description, 0) = 0

The 0 argument in StrComp causes a binary compare which will catch differences between "You" and "you" (for example).

Answer 4

For small data sets, there are any number of approaches using various possible string conversions.

But if your data sets are of any size at all, this will be very slow because it can't use the indexes.

You could possibly optimize by joining case insensitively and then using criteria to test whether the case is the same, e.g.:

  SELECT * 
  FROM a INNER JOIN b ON a.id=b.id
  WHERE Asc(a.id) <> Asc(b.id)

This would at least allow the use of an index join so you wouldn't be comparing "a" to "b" and "a" to "c" (as would be the case with joining on string functions), but only "a" to "a" and "a" to "A".

I would suggest that if your data really needs to distinguish case, then you probably need to store it in a database engine that can distinguish case in joins and then pass your SQL off from Access to that database engine (with a passthrough query, for example).

EDIT:

@apenwarr suggests using StrComp() in the JOIN (as did @butterchicken yesterday), and this SQL raises a question for me (I've updated her/his SQL to use the same table and fieldnames I use above; it's essentially the same as @butterchicken's SQL)

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

It is a fact that Jet will optimize a JOIN on an index exactly the same way it would optimize the equivalent WHERE clause (i.e., implicit JOIN). Stripped down to just the JOIN (presumably on indexed fields), these two SQL statements will be optimized identically by Jet:

  SELECT * 
  FROM a INNER JOIN a
    ON a.id = b.id 

  SELECT * 
  FROM a, b
  WHERE a.id = b.id

My question is whether or not these three will optimize identically:

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
  WHERE StrComp(a.id, b.id, 0) = 0

  SELECT * 
  FROM a, b
  WHERE a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

I'm using SO to avoid work I'm supposed to do for tomorrow, so don't have time to create a sample database and set up SHOWPLAN to test this, but the OP should definitely give it a try and report back on the results (assuming he/she is definitely intending to do this with Jet).

Answer 5

Here's a variation of the earlier suggestion to use StrComp, but this one will start by narrowing down the join using indexes, then restrict it further using StrComp.

SELECT * FROM MyTable A INNER JOIN MyTable B 
on A.description = B.description
 AND StrComp(A.description,B.description, 0) = 0

This should be fast even on large data sets, as long as you don't have lots of entries that are distinguished only by case.