Is there a way to do a not INNER JOIN?

[magickarle]

Hi, I got a table with 10 elements (ie: 1,2,3,4,5,6,7,8,9,10) and
another one with 20 k (ie: 1,20,3,50,11,1,2,10, ...)
I would like to do an INNER JOIN between those 2 tables but would like
to return all elements that are not alike.

Not sure if join can do than.
Thanks

 

[Jerry Whittle]

SELECT tblElements.Element
FROM tblElements
WHERE tblElements.Element not in
(SELECT tblElementsTen.Element
FROM tblElementsTen)
Order by tblElements.Element ;

 

[John Spencer]

If you want to show ALL record in 20K table that are not in the 10 item
table

SELECT A.*
FROM Table20K as A LEFT JOIN Table10items as B
ON A.Element = B.Element
WHERE B.Element is Null

If you want All records the other way round

SELECT B.*
FROM Table20K as A RIGHT JOIN Table10items as B
ON A.Element = B.Element
WHERE A.Element is Nulln

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Of course that example is so trivial that you could do

SELECT A.*
FROM Table20K As A
WHERE Element not In (1,2,3,4,5,6,7,8,9,10)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================