opposite of inner join; exclude rather than match

  • Thread starter Thread starter Christian Bahnsen
  • Start date Start date
C

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.
 
Christian said:
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.
Click to expand...
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
 
Thanks. I'd tried a left outer join but didn't have the "is null" clause.
That was the key.
 
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"
Click to expand...
Click to expand...
 
Back
Top