0

In my table, there are values for poster_display_no 1 and 3 but not 2. I want to fetch the poster_display_no that doesnt exists in the table. The below query is not working as expected. Any idea what is wrong in the above query?

select `poster_display_no` as missing_num 
from `poster-judging-app`.poster_details 
where `poster_display_no` not in (1,2,3)
ssg
  • 69
  • 7
  • 1
    You cannot fetch what is not there – RiggsFolly Dec 16 '19 at 16:33
  • I want to pass a list of poster_display_no and find which is not there in the table. Is that not possible? – ssg Dec 16 '19 at 16:34
  • Sorry for the mistake in the title. I changed the title. I need to find the row that doesn't exists in the table. – ssg Dec 16 '19 at 16:36
  • 1
    Not really. I mean, all things are possible, but an RDBMS is primarily for the storage and retrieval of data. For other things, we have application code. And don't include mathematical operators in table/column identifiers. – Strawberry Dec 16 '19 at 16:36
  • Does this answer your question? [How do I find a "gap" in running counter with SQL?](https://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql) – matthew-e-brown Dec 16 '19 at 16:36
  • @Strawberry. Thanks for your suggestion. It makes sense. I could have approached your way too. `select poster_display_no from poster-judging-app.poster_details where poster_display_no in (1,2,3)` and handle the missing value logic in the application code. – ssg Dec 16 '19 at 17:15

1 Answers1

2

This is kind of a hack, but you could build a derived table with the list of poster_display_nos that you want to chek for, left join with your table and filter on the missing ones:

select t.poster_display_no
from (
    select 1 poster_display_no
    union all select 2
    union all select 3
) t
left join poster_details p on p.poster_display_no = t.poster_display_no
where p.poster_display_no is null

Another, more scalable option would be to create a separate referential table to store the list of poster_display_nos that you want to check for, and then bring that table directly to the query.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • The @GMB idea of joining with the is null check is correct: select all the possible 1,2,3 values left join the `poster-details` table where the `poster-details`.`poster_display_no` table column is null retrieves the missing values SELECT num FROM ( SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 ) AS temp LEFT JOIN poster-details ON (num=poster_display_no) WHERE poster_display_no IS NULL; – Ruth Dec 16 '19 at 16:46