CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
meallhour
meallhour

Reputation: 15571

selecting only records which are not present in the second table

I have a mysql table1 with hostname field and the following values:

HostName
-----
sa77.com
ca77cded.com
sa65yacd.com
ca65zededs.com
sa88y.com
sa99ujk8.com

I have another table2 which has name field(carries hostname values). 

Name
-----
sa77
ca77cded.com
sa65yacd
ca65zededs.com

I want to select records from table2 which are not present in table1

select * 
from table2 
where name NOT IN (select hostname from table1);

The server name within name or hostname in either of 2 tables may or maynot be fully qualified. For example, sa77 can have value as sa77.abc.com or sa77.cde or sa77. The server can have multiple domain values

sa77.abc.com matches sa77 and sa77.abc I basically need to compare for the value sa77

Upvotes: 0

Views: 55

Answers (2)

Brian Dewhirst
Brian Dewhirst

Reputation: 307

I generally do this using a left join and a where is null, like this:

select * 
from table2
left join table1 on table2.name=table1.hostname
where table1.hostname is null;

(edit because you want records from table2 not in table1, rather than the reverse.)

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

what about: 

where name NOT IN (select hostname from table1)
  and CONCAT(name, '.com') NOT IN (select hostname from table1);

But you need explain a litle more about what cases you need to match.

Upvotes: 0

Related Questions