Re: outer Join and NULL values...?!?!!!

What Vanderghast said!

Functions in query expressions is one of Jet's great
features. Just don't rely on it if you want to port your db
to another db engine.

The query designer has no way for you to specify the
potentially complex ON clauses that are acceptable in an SQL
statement. Not only functions, but non-equi joins also
require SQL view to create. One of my favorites is to join
records on inexact matches using:
ON tbl1.fldx Like tbl2.patternfield
I don't know about you, but it kind of boggles my mind what
can be done with this kind of thing.
--
Marsh
MVP [MS Access]


Mark Burns wrote:
<epiphany moment>
You can use functions/expressions in the ON phrase of a JOIN expression in
JET SQL??!!
</epiphany moment>
Well DANG! Why didn't somebody tell me that a few years ago?
(and why can't the Query designer give me a clue/ability to do that more
easily!?)

YOU are one Awesome dude, V.! Thanks!

"Michel Walsh" wrote:
If you want null matching another null, you can coalesce, Nz(), them to a
value that is known to not present in your data, such as -1 for a field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what the
introduction of the 0 has been 400 years ago (in Europe, that is). And since
no one is born with that knowledge, many of us, like you, just got "that
moment". And since it would hardly kill you, I hope it won't, it will just
make you stronger! :-)


"Mark Burns" <MarkBurns@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
So, just so I have this straight, according to Jet's internal Join
arithmatic,
Table B Table C
F2 = "Smith" = -> F2 = "Smith"
F3 = Null = -> F3 = Null
F4 = "QA" = -> F4 = "QA"
...in an outer join = "No Match"?
...but
Table B Table C
F2 = "Smith" = -> F2 = "Smith"
F3 = Null F3 = Null
F4 = "QA" = -> F4 = "QA"
...in an outer join = "Match"?

...I just KNEW these damn Nulls would drive me INSANE one day...

So, how do you suggest I handle that small %age of data records where F3 =
Null?
...build another, separate query to handle just those records? <Grrrr...>


"Marshall Barton" wrote:

Mark Burns wrote:

OK first the question: why are the NULLs screwing up my query results?

The problem:
I am constructing a series of append queries to dissect non-normal data
from
some older tables to fit it all into a new normalized structure. I have
3
tables:
A, B, and C. I am importing data from table B into table C (referencing
table A in the process).
To avoid importing duplicate records I am joining tables B and C in my
query
as an outer join B.F2->C.F2 + B.F3->C.F3 + B.F4->C.F4 (the query also
matches
B.F1->A.F3, but that's not the problem here... also, importantly, I
check
for B.F2 is not null AND C.F1 IS Null).
When I run this query the FIRST time, I get 80 records inserted into
table C
as expected.
I expect that when I run this query again immediately, I will get ZERO
records qualified (because all the outer joins will now match table C
records
which maked C.F1 NOT Null.
...BUT I still get 15 records qualifying to be inserted into Table C
from
Table B!
The 15 records all contain NULL values in B.F3 (and, in C.F3).
I know this is the problem because if I remove the Outer Join B.F3->C.F3
I
get exactly the ZERO qualifying records I expect?!! HUH??!!
So...Jet 4.0 does not think Null=Null here, or what??
(and...they are all nulls, the fields are set 'allow 0 length strings =
false')


Well, it is true that Null = Null is never True. It is also
the case that Null <> Null is never True.

Think of it this way. Null sort of means Unknown. So, is
an unknown value equal (or not equal) to another unknown
value? The answer is unknown so it can not be either True
or False.
.

Relevant Pages

  • Re: outer Join and NULL values...?!?!!!
    ... If you want a null matching any thing: ... ...in an outer join = "No Match"? ... separate query to handle just those records? ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)
  • Re: optimising the following query
    ... For this query to perform well, the optimal would be a clusterd index ... on (transctiondate, servicename) or. ... CASE ua WHEN 'unknown' THEN NULL ELSE ua END AS ua ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: outer Join and NULL values...?!?!!!
    ... (and why can't the Query designer give me a clue/ability to do that more ... ...in an outer join = "No Match"? ... ...BUT I still get 15 records qualifying to be inserted into Table C ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)
  • Re: outer Join and NULL values...?!?!!!
    ... ....in an outer join = "No Match"? ... To avoid importing duplicate records I am joining tables B and C in my query ... ...BUT I still get 15 records qualifying to be inserted into Table C from ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)
  • Re: outer Join and NULL values...?!?!!!
    ... I am constructing a series of append queries to dissect non-normal data from ... When I run this query the FIRST time, I get 80 records inserted into table C ... ...BUT I still get 15 records qualifying to be inserted into Table C from ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)