How to find rows in a table that are not in other table

Question

I have two tables:

1. staticip has two columns: ip, staticipid

2. rm_users has many columns and one of it is staticipcpe

I would like to find rows in staticip table that is staticipid='2' and ip is not equal staticipcpe in table rm_users.

I tried the following sql statement but it didn't work.

$sqls="select s.ip, s.staticipid from staticip s 
            where s.staticipid='2' and not exists 
            (select u.staticipcpe from rm_users u 
            where u.staticipcpe=s.ip";

I have the following message

"Warning: mysql_result(): supplied argument is not a valid MySQL result resource"

Answer 1

Among other possible problems, you're missing a closing parenthesis:

$sqls="select s.ip, s.staticipid from staticip s 
        where s.staticipid='2' and not exists 
        (select u.staticipcpe from rm_users u 
        where u.staticipcpe=s.ip)";

Also, the mysql_* functions are deprecated. Use MySQLi or PDO.

Answer 2

if you query is right you can use NOT IN Clause to achieve your goal

like

Select Id
From Test
Where Id Not In( Select Foo From Bar )

see for more info

MySQL "NOT IN" query

Answer 3

Depending on your database a JOIN may be better over a sub-query (see Join vs. sub-query), and I also favor backticks (Importance of backtick around table name in MySQL query):

SELECT `s`.`ip`,`s`.`staticipid`
FROM `staticip` AS `s`
INNER JOIN `rm_users` AS `u`
ON `u`.`staticipcpe`<>`s`.`ip`
WHERE `s`.`staticipid`='2';

In plain English:

• SELECT: grab the fields "ip" and "staticipid" from the result
• FROM: the primary table will be "staticip" - alias as "s"
• INNER JOIN: compare the rows in "staticip" with the rows in "rm_users"
• ON (part of INNER JOIN): fetch the rows where "staticipcpe" from "ip" is not listed under the field "staticipcpe" from "rm_users"
• WHERE: filter results such that the value of "staticipid" must be "2"

---

Don't forget to consider indexing the fields rm_users.staticipcpe, staticip.staticipid (I assume this one is already a PRIMARY KEY), and staticip.ip, otherwise you're looking at full table scans, rather than taking advantage of MySQL's b-tree lookup from memory.