CODEWITHSUNDEEP
mysqlsql
Himanshu Upadhyay
Himanshu Upadhyay

Reputation: 743

Get missing id from table

I have tbl_profilecategory table structure like this:

Id      col1
1       50
3       30
4       40
5       50
8       80
9       90

Now I want the missing IDs from table, so the result here should be like this:

Id= 2,6,7

Upvotes: 1

Views: 893

Answers (2)

Cary Bondoc
Cary Bondoc

Reputation: 2988

This answer is based here

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)

Upvotes: 1

Kroonal
Kroonal

Reputation: 360

You can use 

SELECT t1.id - 1 AS missing_id
FROM mytable t1
LEFT JOIN mytable t2 ON t2.id = t1.id - 1
WHERE t2.id IS NULL

Upvotes: 2

Related Questions