CODEWITHSUNDEEP
sqlmysql
user453984
user453984

Reputation: 133

MySQL check id whether is in another table

How to check whether the id from one table is in another table in the same database. If it is then the row should not be returned.

table1: id int(11) unsigned primary key, data varchar(25) default ''

table2: id int(11) unsigned primary key, tableone_id int(11) unsigned, another_data varchar(11) default''

the query checks whether id from table one is in table two table (the fields compared are table1.id and table2.tableone_id .

Upvotes: 1

Views: 2898

Answers (2)

LukeH
LukeH

Reputation: 269618

SELECT table1.*
FROM table1
    LEFT JOIN table2
        ON table1.id = table2.tableone_id
WHERE table2.tableone_id IS NULL

Upvotes: 2

Dan G
Dan G

Reputation: 1090

Not sure if it's the most efficient, but:

SELECT * FROM table2 WHERE table2.tableone_id NOT IN (SELECT id FROM table1)

Upvotes: 1

Related Questions