Is there a way to do a not INNER JOIN?

  • Thread starter Thread starter magickarle
  • Start date Start date
M

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
 
SELECT tblElements.Element
FROM tblElements
WHERE tblElements.Element not in
(SELECT tblElementsTen.Element
FROM tblElementsTen)
Order by tblElements.Element ;
 
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
'====================================================
 
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
'====================================================
 
Back
Top