CODEWITHSUNDEEP
mysqlsql
Matt Elhotiby
Matt Elhotiby

Reputation: 44066

mysql - Query to find out which ones i dont have?

Ok I have a table people and I have and Id column with thousands of record... I have another list of comma seperated Ids like this 

1, 2, 457, 558, 998

I want to check this people table to see which of the 5 records is not present...

I tried 

select id from people where id not in (1, 2, 457, 558, 998)

but this returns all the other thousand records and not just the records not found in these 5

any ideas what i am missing

Upvotes: 1

Views: 159

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

select a.id
from (
    select 1 as id
    union all
    select 2
    union all
    select 457
    union all
    select 558
    union all
    select 998
) a 
left outer join people p on a.id = p.id
where p.id is null

If the values you want to check against are in a table, you can do this:

select c.id
from MyCheckValues c
left outer join people p on c.id = p.id
where p.id is null

Upvotes: 2

a coder
a coder

Reputation: 7639

SELECT id FROM people WHERE id IN (1, 2, 457, 558, 998)

If you have multiples of each "id", then use: 

SELECT DISTINCT(id) FROM people WHERE id IN (1, 2, 457, 558, 998)

Upvotes: 0

Randy
Randy

Reputation: 16677

maybe something like this:

Select id from people where id in (1, 2, 457, 558, 998)
and id not in ( select id from people )

Upvotes: 1

Related Questions