CODEWITHSUNDEEP
mysqlsqlmatch
kairos
kairos

Reputation: 522

Match values from two different tables

Given 2 tables, TableA and TableB, how can I get the "id"s of TableA when the "value" doesn't match with any "value" of TableB?

Table A

+----+-------+
| id | value |
+----+-------+
| 1  |   a   |
| 2  |   b   |
| 3  |   c   |
| 4  |   d   |
| 5  |   e   |
+----+-------+

Table B

+----+-------+
| id | value |
+----+-------+
| 1  |   a   |
| 2  |   b   |
| 3  |   c   |
| 4  |   c   |
| 5  |   f   |
+----+-------+

Result Table

+----+
| id |
+----+
| 4  |
| 5  |
+----+

EDITED (SQLFiddle): http://sqlfiddle.com/#!2/4c8c9

Upvotes: 2

Views: 2431

Answers (2)

Taryn
Taryn

Reputation: 247690

If you want to validate that the id/value are the same in both tables then use:

select distinct id
from tablea
where (id, value) not in (select id, value
                          from tableb)

See SQL Fiddle with Demo

If you want to compare only the values:

select distinct id
from tablea
where (value) not in (select value
                          from tableb)

See SQL Fiddle with Demo

Upvotes: 5

juergen d
juergen d

Reputation: 204766

select a.id
from tableA a
left outer join tableB b on a.id = b.id and a.value = b.value
where a.id is not null and b.value is null

SQLFiddle demo

Upvotes: 3

Related Questions