OUTER JOIN HELP

[brents555 via AccessMonster.com]

Hi, I'm new at this, so I'll try and explain what I'm trying to do here,
maybel I"m going down the wrong road. In my instance a member equals a
customer. Just so you know.


I built a query which is pulling member history data for members with bad
payment history. (A-Del-Members-List)

I built another query which pulls data from a table (MEMBERDETL), but I want
to exclude
members that exist on the query above. Basically, excluding the members with
any bad payment history.

What I've got right now, is the following:

SELECT DISTINCT CAV_MEMBERDETL.MBRNO, CAV_MEMBERDETL.MBRFEE, CAV_MEMBERDETL.
CONSDEP, CAV_MEMBERDETL.MBRSEP
FROM CAV_MEMBERDETL LEFT OUTER JOIN [A-Del-Members-List] ON CAV_MEMBERDETL.
MBRSEP = [A-Del-Members-List].MBRSEP
WHERE (((CAV_MEMBERDETL.MBRFEE)>0) AND ((CAV_MEMBERDETL.CONSDEP)>0));

To achieve this, I've attempted to run a LEFT OUTER JOIN, which I thought
would only retrieve the rows from the left table that do not meet the join
criteria after the ON keyword. At this point, I'm still getting rows back
that exist on the A-Del-Members-List query, which I do not want. Is this
method completely wrong? If so, is there a way to do this?

 

[Michel Walsh]

You need to add

AND [A-Del-Members-List].MBRSEP IS NULL

to the where clause.


Hoping it may help,
Vanderghast, Access MVP

 

[brents555 via AccessMonster.com]

Michel,
I"m going to go try that, but can you explain to me why I need to check
that field for a NULL value?
Just curious.

You need to add

AND [A-Del-Members-List].MBRSEP IS NULL

to the where clause.

Hoping it may help,
Vanderghast, Access MVP

Hi, I'm new at this, so I'll try and explain what I'm trying to do here,
maybel I"m going down the wrong road. In my instance a member equals a

[quoted text clipped - 25 lines]

that exist on the A-Del-Members-List query, which I do not want. Is this
method completely wrong? If so, is there a way to do this?

 

[brents555 via AccessMonster.com]

Michel,
It looks like that worked like a charm.. I still want to know why that
works? At least it'll get the logic of how that join is working cleared up.
Thanks for your help!!!!

Michel,
I"m going to go try that, but can you explain to me why I need to check
that field for a NULL value?
Just curious.

You need to add

[quoted text clipped - 10 lines]

 

[Michel Walsh]

The WHERE criteria is applied to the row AFTER the join. For an outer join,
the unpreserved table may have supplied a value of its own, if at least one
row was getting the ON clause evaluated to true, or it is a NULL if no such
row was in the table. So, we do not check necessary the initial table, but
the result of the join.

So, checking for a NULL, under the unpreserved table, would mean that
either the null was supplied by the table, either it is supplied by the
outer-ness of the join. Since the field was implied in a = comparison in the
ON criteria, if it was NULL, the ON would have evaluated to NULL too.

We can so safely said that testing against NULL for the field (from the
unpreserved table) implied in the on clause is like asking for a value
supplied by the outer-ness of the join, so, a not-matching row (match
defined by the ON criteria).



Hoping it may help,
Vanderghast, Access MVP

Michel,
It looks like that worked like a charm.. I still want to know why that
works? At least it'll get the logic of how that join is working cleared
up.
Thanks for your help!!!!

Michel,
I"m going to go try that, but can you explain to me why I need to
check
that field for a NULL value?
Just curious.

You need to add

[quoted text clipped - 10 lines]

that exist on the A-Del-Members-List query, which I do not want. Is
this
method completely wrong? If so, is there a way to do this?