Getting missing values from in clause

Question

I have to find all those records for which I provide ids, so here is my query

SELECT * FROM `inf_company` where id IN(25523,25511,25448,28094,25243.....)

I provide 500 Ids in above query but it give me 200 records. I want to find rest of missing 300 records.

I want to see all those ids in my list which is not shown in result. How could be the query for that?

Is it problem in PMA or your project? Сheck the settings. Try add LIMIT 0, 500 — viktor.svirskyy
– viktor.svirskyy, Commented Aug 26, 2014 at 7:22
Build a (temporary) table of the desired ids and join it on to this table. — Strawberry
– Strawberry, Commented Aug 26, 2014 at 7:25
Where does that list of ids come from in the first place? I doubt you have written them manually, right? If they come from another table, can't you LEFT JOIN from there? (Oh... and please edit the title... as it is it's absolutely meaningless) — Frazz
– Frazz, Commented Aug 26, 2014 at 7:36

Community · Accepted Answer · 2017-05-23 12:21:22Z

1

You could write a union for the id fields and left outer join to get actual result:

select id
,      case
       when i1.id is not null
       then 'EXISTING'
       else 'NON-EXISTING'
       end
       status
from   ( select 25523 id union select 25511 union select 25448 /* etc, etc */ ) x
left
outer
join   inf_company i1
on     i1.id = x.id

You could also put the ids in a temporary table as suggested by Strawberry. That will prevent the long list of union on id.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Aug 26, 2014 at 7:27

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3244721 Over a year ago

you asked me to write select 25523 id union select 25511 manually?should i write all 500 ids here?

Patrick Hofman Over a year ago

Yes, just as in your current query, or preferably insert them in a temporary table. In this cases Excel may come in handy to fabricate the query.

Patrick Hofman Over a year ago

@user3244721: Did this solve your issue? Need more help?

user3244721 Over a year ago

I am doing it manually in excel...by query it is not possible

Patrick Hofman Over a year ago

What is your problem with this query?

|

Collectives™ on Stack Overflow

Getting missing values from in clause

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related