I have two tables:

tableA has a field numberA, which has rows:
1
2
4

tableB has a field numberB, which has rows:
1
2
3

When I use SQL:

select numberB
from tableA and tableB
where tableA.numberA <> tableB.numberB

I expect to output 3 only, but actually, I output many numbers. It seems to be that for each row in tableA, it looks for all different numbers in tableB. My question is what is the simple way to output 4 in tableB only?

This may be a trivial question. Many thanks to any suggestion.

Does these tables have ant Primary Keys..? If you join with the given structure, you will get many records.

Om Prakash

Does this help (put a primary key in both tables and join them one-to-one in the relationships window)?

SELECT DISTINCTROW TableB.NumberB
FROM TableA INNER JOIN TableB ON TableA.TableAID = TableB.TableBID
WHERE ((([numberA]-[NumberB])<>0));


Clive Astley

This query will accomplish what you are trying to do:

SELECT TableA.numberA, TableB.numberB
FROM TableA LEFT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableB.numberB) Is Null));

Jack, this doesn't appear to give the result Wanda says he is expecting?

Clive Astley

I guess it depends on how we interpret the question.
In start of question there is reference to 3. At end of question, it is how do I get the 4???


In any event,

1)give me the numbers in TableA that are not in TableB

SELECT TableA.numberA, TableB.numberB
FROM TableA LEFT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableB.numberB) Is Null));
 result -->4

2) give me the numbers in TableB that are not in TableA

 SELECT TableA.numberA, TableB.numberB
FROM TableA RIGHT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableA.numberA) Is Null));
  result-->3

jack d