Re: Inner join on table with NULL's

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Carl Colijn" <carl.colijn@xxxxxxxxx> wrote in message
news:nlJEg.4842$YI3.1209@xxxxxxxxxxxxx
< snipped >

The problem is that Access (and I believe all SQL implementations) cannot
deal with NULL values at all when using a JOIN, so that "table1.column =
table2.column" cannot be used as a join condition if they both contain
NULL (NULL <> NULL in SQL, by design I believe).

Anyway, thanks for the suggestion,
Kind regards,
Carl Colijn


The "trick" in this case is to use "nonequijoin" utilizing the Nz function
to replace a Null value with a "unique" value that will tell you the value
is Null. For example, suppose that the field contains only positive
number -- you could use a query like this:

SELECT T1.*, T2.*
FROM Table1 AS T1
INNER JOIN Table2 AS T2
WHERE Nz(T1.FieldName, -100) =
Nz(T2.FieldName, -100);

Similar steps could be used for text:

SELECT T1.*, T2.*
FROM Table1 AS T1
INNER JOIN Table2 AS T2
WHERE Nz(T1.FieldName, "MyNullValue") =
Nz(T2.FieldName, "MyNullValue");

--

Ken Snell
<MS ACCESS MVP>


.

Relevant Pages

  • Re: query
    ... FROM Table1 as T1 ... INNER JOIN Table2 as T2 ... xbcd 6 8 ... k1 bcd cfgr xnyu ...
    (microsoft.public.access.queries)
  • Re: count in a query
    ... give a syntax error with the extra ")" at the end.. ... Beyond that I see no way to speed up the query. ... INNER JOIN Table2 b ... FROM Table1 a where Code in (Select b.Code from Table2 b where ...
    (microsoft.public.access.queries)
  • Re: SQL select query question
    ... FROM Table1 T1 ... INNER JOIN Table2 T2 ... SQL Server Programmer ... Or how do I write the query if I have two ...
    (microsoft.public.sqlserver.programming)
  • Join query returns to many rows
    ... the columns in table1 the correct number of records are returned but when ... inner join table2 b ...
    (microsoft.public.sqlserver.programming)