opposite of inner join; exclude rather than match

[Christian Bahnsen]

Essentially, I’m trying to do the opposite of an inner join, to exclude the
values from table A that have matching values in table B, returning all the
other records from Table A, rather than return the records that exist in both
tables.

Using the text below as an example, an inner join of Table A to Table B on a
key field would return 2 and 5. What I’m trying to do is return all the
values from Table A except 2 and 5.

Table A Table B
1 76
2 2
3 44
4 6
5 5
… …


I tried replacing the = of an inner join with <> or !=, but Access doesn’t
seem to like that, telling me it can’t represent <> and rejecting != out of
hand.

Thanks in advance for any help.

 

[Bob Barrows [MVP]]

Essentially, I'm trying to do the opposite of an inner join, to
exclude the values from table A that have matching values in table B,
returning all the other records from Table A, rather than return the
records that exist in both tables.

Using the text below as an example, an inner join of Table A to Table
B on a key field would return 2 and 5. What I'm trying to do is
return all the values from Table A except 2 and 5.

Table A Table B
1 76
2 2
3 44
4 6
5 5
. .


I tried replacing the = of an inner join with <> or !=, but Access
doesn't seem to like that, telling me it can't represent <> and
rejecting != out of hand.

It's called an outer join

Select a.fieldname from tale1 as a left outer join table2 as b
on a.fieldname = b.fieldname where b.fieldname is null

 

[Christian Bahnsen]

Thanks. I'd tried a left outer join but didn't have the "is null" clause.
That was the key.

 

[venkatesh s]

Dear All,
Thanks for providing the logic.... where colmn is null......


By
S.Venkatesh

Essentially, I???m trying to do the opposite of an inner join, to exclude the
values from table A that have matching values in table B, returning all the
other records from Table A, rather than return the records that exist in both
tables.

Using the text below as an example, an inner join of Table A to Table B on a
key field would return 2 and 5. What I???m trying to do is return all the
values from Table A except 2 and 5.

Table A Table B
1 76
2 2
3 44
4 6
5 5
??? ???


I tried replacing the = of an inner join with <> or !=, but Access doesn???t
seem to like that, telling me it can???t represent <> and rejecting != out of
hand.

Thanks in advance for any help.

--
Christian Bahnsen

It's called an outer join

Select a.fieldname from tale1 as a left outer join table2 as b
on a.fieldname = b.fieldname where b.fieldname is null

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"