left join problem in XP

[Brigitte P]

I have done many times left or right outer joins, and things worked fine.
Now they don't, and I found on the Microsoft support site that this is a
known bug and one should download the latest ServicePack for Jet 4 - Service
Pack 8. I have 4.00.8618.0 which should be fine. But I did another download
and run it to problems. It would not install from my computer (not a valid
Win32 application), so I installed it from the download site. It didn't make
any difference. I tried the work around from Article 275058, but that didn't
work either maybe because I'm SQL challenged. Here is the SQL from the
original query:
SELECT tblLookupInfectionType.INFCODE1, tblLookupInfectionType.INFNOTE1,
tblInfection.INFDATE
FROM tblLookupInfectionType LEFT JOIN tblInfection ON
tblLookupInfectionType.INFCODE1 = tblInfection.INFCODE1
WHERE (((tblInfection.INFDATE) Between [Forms]![frmStartup]![txtStartDate]
And [Forms]![frmStartup]![txtEndDate]));

According to 275058, the work around should include a Subquery entered after
the LEFT JOIN [SELECT * FROM tblLookupInfectionType WHERE INFCODE >0] - this
is my interpretation of the workaround for my application.

What I want are all records from tblLookupInfectionType and then the records
from tblInfections where the Date is pulled from frmStartup .......

The result should be a set of records without a date from the tblInfections
for some records (this means that the infection type did not occur during
the reporting period). As I said, I have done it many times in A97
databases, but I'm afraid that after conversion to XP, all of our databases
with outer joins maybe wrong because of the bug.
Help, please.
Brigitte P.

 

[[MVP] S.Clark]

It has always been my experience that adding criteria to an outer join makes
it behave like an inner join. A2.0 -> A2k3.

I typically use more than one query when this situation arises. One to apply
the criteria, the other to do the join.

 

[Brigitte P]

Thanks. That is what I finally did. It still seems that right join somehow
does better, don't know why. Anyway, this is a lesson learned for the future.
Thanks again.
Brigitte P.

It has always been my experience that adding criteria to an outer join makes
it behave like an inner join. A2.0 -> A2k3.

I typically use more than one query when this situation arises. One to apply
the criteria, the other to do the join.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have done many times left or right outer joins, and things worked fine.
Now they don't, and I found on the Microsoft support site that this is a
known bug and one should download the latest ServicePack for Jet 4 -
Service
Pack 8. I have 4.00.8618.0 which should be fine. But I did another
download
and run it to problems. It would not install from my computer (not a valid
Win32 application), so I installed it from the download site. It didn't
make
any difference. I tried the work around from Article 275058, but that
didn't
work either maybe because I'm SQL challenged. Here is the SQL from the
original query:
SELECT tblLookupInfectionType.INFCODE1, tblLookupInfectionType.INFNOTE1,
tblInfection.INFDATE
FROM tblLookupInfectionType LEFT JOIN tblInfection ON
tblLookupInfectionType.INFCODE1 = tblInfection.INFCODE1
WHERE (((tblInfection.INFDATE) Between [Forms]![frmStartup]![txtStartDate]
And [Forms]![frmStartup]![txtEndDate]));

According to 275058, the work around should include a Subquery entered
after
the LEFT JOIN [SELECT * FROM tblLookupInfectionType WHERE INFCODE >0] -
this
is my interpretation of the workaround for my application.

What I want are all records from tblLookupInfectionType and then the
records
from tblInfections where the Date is pulled from frmStartup .......

The result should be a set of records without a date from the
tblInfections
for some records (this means that the infection type did not occur during
the reporting period). As I said, I have done it many times in A97
databases, but I'm afraid that after conversion to XP, all of our
databases
with outer joins maybe wrong because of the bug.
Help, please.
Brigitte P.

 

[Michel Walsh]

Hi


This is not a bug, even if the article refers it as such. The nature of the
LEFT/RIGHT join is to add NULL on the UNPRESERVED side. In your case,
tblInfection fields may have NULL ... because of the LEFT JOIN. Your WHERE
clause is built on tblInfection but does not leave any room for those NULL:

NULL BETWEEN a AND b


returns NULL. Thus, you destroy any effect of the outer join and the end
result is as if you would have use an INNER JOIN.

The same behavior exists for MS SQL Server.


If you have a condition, in the WHERE clause, implying the unpreserved
side, allows for NULL, or use a virtual table (ie, solve the WHERE clause
before making the join):

Instead of

SELECT ...
FROM a LEFT JOIN b ON ...
WHERE b.f1 = 444


try:

SELECT ...
FROM a LEFT JOIN b ON ...
WHERE Nz(b.f1 = 444, -1)


or


SELECT
FROM a LEFT JOIN (SELECT * FROM b WHERE f1=44) As c
ON ...



Hoping it may help,
Vanderghast, Access MVP

 

[Brigitte P]

Thank you. I'll try this, and I think I can follow the logic - as I said,
I'm SQL and VBA challenged. Currently, I have solved the problem by building
the query on the query as suggested by S. Clark. This worked, but I'm
reassured that this is not a bug (even though the KB article states this)
because this means that my A97 databases that I converted to XP created with
working outer joins will still be giving the right results to my users. But
I'll try your suggestions because I always learn things from the suggestions
and remember what I've learned usually (not always) for the future. Thanks
again.
Brigitte P.

Hi


This is not a bug, even if the article refers it as such. The nature of the
LEFT/RIGHT join is to add NULL on the UNPRESERVED side. In your case,
tblInfection fields may have NULL ... because of the LEFT JOIN. Your WHERE
clause is built on tblInfection but does not leave any room for those NULL:

NULL BETWEEN a AND b


returns NULL. Thus, you destroy any effect of the outer join and the end
result is as if you would have use an INNER JOIN.

The same behavior exists for MS SQL Server.


If you have a condition, in the WHERE clause, implying the unpreserved
side, allows for NULL, or use a virtual table (ie, solve the WHERE clause
before making the join):

Instead of

SELECT ...
FROM a LEFT JOIN b ON ...
WHERE b.f1 = 444


try:

SELECT ...
FROM a LEFT JOIN b ON ...
WHERE Nz(b.f1 = 444, -1)


or


SELECT
FROM a LEFT JOIN (SELECT * FROM b WHERE f1=44) As c
ON ...



Hoping it may help,
Vanderghast, Access MVP

I have done many times left or right outer joins, and things worked fine.
Now they don't, and I found on the Microsoft support site that this is a
known bug and one should download the latest ServicePack for Jet 4 -
Service
Pack 8. I have 4.00.8618.0 which should be fine. But I did another
download
and run it to problems. It would not install from my computer (not a valid
Win32 application), so I installed it from the download site. It didn't
make
any difference. I tried the work around from Article 275058, but that
didn't
work either maybe because I'm SQL challenged. Here is the SQL from the
original query:
SELECT tblLookupInfectionType.INFCODE1, tblLookupInfectionType.INFNOTE1,
tblInfection.INFDATE
FROM tblLookupInfectionType LEFT JOIN tblInfection ON
tblLookupInfectionType.INFCODE1 = tblInfection.INFCODE1
WHERE (((tblInfection.INFDATE) Between [Forms]![frmStartup]![txtStartDate]
And [Forms]![frmStartup]![txtEndDate]));

According to 275058, the work around should include a Subquery entered
after
the LEFT JOIN [SELECT * FROM tblLookupInfectionType WHERE INFCODE >0] -
this
is my interpretation of the workaround for my application.

What I want are all records from tblLookupInfectionType and then the
records
from tblInfections where the Date is pulled from frmStartup .......

The result should be a set of records without a date from the
tblInfections
for some records (this means that the infection type did not occur during
the reporting period). As I said, I have done it many times in A97
databases, but I'm afraid that after conversion to XP, all of our
databases
with outer joins maybe wrong because of the bug.
Help, please.
Brigitte P.