How to get from sub query where id does not exist

Question

Hi, I have a sql query that is retrieving results from a table but I need it to only show results if the ids are not in another table.

Example

$sql = "SELECT id FROM TABLE_1 WHERE id NOT IN(SELECT more_id FROM TABLE_2)

The idea is that if the id does not exist in the list of "more_id" then it should show the result.

It does not seem to work, any help would be greatly appreciated. I should mention that "more_id" is just the same "id" but in another table that stores other records.

Answer 1

This should work:

$sql = "SELECT TABLE_1.id
            FROM TABLE_1
            LEFT JOIN TABLE_2
                ON TABLE_1.id = TABLE_2.more_id
            WHERE TABLE_2.more_id IS NULL"

Without the WHERE clause, you will end up with a list of all items in TABLE_1, including both the ones with matches in TABLE_2 and those without. Adding the WHERE clause filters the matches out.

Answer 2

My best bet is one table is using VARCHAR as id, the other table is using INTEGER,CHAR or other incomparable types.