How get items which all not present in mysql Table in a range

Question

I have an mysql table where Ids are between 1 to 1815. but some of the Ids are not available. For example 15, 22, 55 etc..

How to get those ids??

I tried

select product_id from table_products where product_id not BETWEEN 1 and 1815

But its not working.

What do you mean "not available"? There's no record on product_id = 15? — Derenir, Sep 17 '15 at 13:56
possible duplicate of [How to find gaps in sequential numbering in mysql?](http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql) — Mauker, Sep 17 '15 at 13:56

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

If it doesn't exists in table then you can hard code the values using UNION like below. Well, I had no idea about How to find gaps in sequential numbering in mysql?. Which looks really nice and probably a much better solution than what I am proposing.

select product_id 
from table_products 
where product_id BETWEEN 1 and 1815

UNION

select 15 as product_id from dual

UNION

select 22 as product_id from dual

UNION

select 55 as product_id from dual

edited May 23 '17 at 11:58

Community

1
1

answered Sep 17 '15 at 13:57

Rahul

76,197
13
71
125

Hard coding? I just need those ids? you mean to say i manually scan table and find all the ids and then union them? – Devesh Agrawal Sep 17 '15 at 13:59
@DeveshAgrawal, Yes you got it else see another post link in answer for finding gaps. Cause, those values doesn't exists in table and so you will have generate them somehow. – Rahul Sep 17 '15 at 14:01

score 0 · Answer 2 · edited May 23 '17 at 12:06

I found this answer in How to find gaps in sequential numbering in mysql? and found it really does the trick (adapted for your purpose):

    SELECT
 CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
      SELECT
      @rownum:=@rownum+1 AS expected,
      IF(@rownum=table_products.product_id, 0, @rownum:=table_products.product_id) AS got
     FROM
      (SELECT @rownum:=0) AS a
      JOIN table_products
      ORDER BY table_products.product_id
     ) AS z
    WHERE z.got!=0;

Hope this does the job for you. The only thing it does not do is stop at a particular product_id, but I assume you can look at the result and take the data that you need.

How get items which all not present in mysql Table in a range

2 Answers2