CODEWITHSUNDEEP
mysql
bart2puck
bart2puck

Reputation: 2522

MySQL: finding a record not present in another table

I have 2 MySQL tables:

id   customer  ipAddress
--------------------------
1    acme      10.10.10.10
2    target    33.33.33.33

number   ip
--------------------
54321    10.10.10.10
41247    33.33.33.33
62068    77.77.77.77

In this case, 77.77.77.77 has no entries in table 1.

How can I get all the numbers in table 2 that do not have an ipAddress in table 1?

I have tried:

select ip from table1,table2 where ip not in(select ipAddress from table1);

but I get an empty set.

Upvotes: 0

Views: 55

Answers (2)

PM 77-1
PM 77-1

Reputation: 13352

"Records without match" problems very often can be solved with a simple OUTER JOIN and then WHERE ... IS NULL.

In you case:

SELECT t2.ip
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.ipAddress = t2.ip
WHERE t1.ipAddress IS NULL ;

SQL Fiddle DEMO

Upvotes: 0

Pierre-Olivier Benoit
Pierre-Olivier Benoit

Reputation: 149

I got a correct answer (77.77.77.77) with a left join and a where is null:

select ip from table2 left join table1 on (ip = ipAddress) where ipAddress is null;

Upvotes: 3

Related Questions