CODEWITHSUNDEEP
sqlms-access
Emman D.
Emman D.

Reputation: 117

MS Access SQL to return values in Table2 that are not found in Table1

I know this is a very trivial question, but I can't seem to find the right commands to get the result shown below. I could not understand how to use NOT EXIST or IN. Note that I am using MS Access.

The following gives errors:

SELECT T2.* FROM T2
WHERE NOT EXIST
(SELECT T1.* FROM T1)

I have no one else to ask personally, since I am just learning SQL from YouTube. Hope anyone could help me. 

T1
+-----+------+
| Ind | Val  |
+-----+------+
|  1  |  aa  |
|  2  |  bb  |
+-----+------+

T2
+-----+------+
| Ind | Val  |
+-----+------+
|  1  |  aa  |
|  2  |  bb  |
|  3  |  cc  |
+-----+------+

Result:
+-----+------+
| Ind | Val  |
+-----+------+
|  3  |  cc  |
+-----+------+

Upvotes: 0

Views: 42

Answers (2)

Mr.No
Mr.No

Reputation: 83

Why don't you use JOIN. It is much more useful and will work faster on larger tables. More joins here.

select *
from T1 a
right join T2 b on b.key = a.key
where a.key is null

Upvotes: 1

Erik A
Erik A

Reputation: 32632

If you're going to use a NOT EXISTS, you need to specify which columns you're comparing:

SELECT T2.* FROM T2
WHERE NOT EXIST
(SELECT 1 FROM T1 WHERE T1.ind = T2.ind AND T1.Val = T2.Val)

Upvotes: 1

Related Questions