sql query to get deleted records
gaps-and-islands, mysql, select, sql
Solution
I think easiest would be to have a dummy/temp table with just ids. 1-1000 then left join to that table.
But be sure to remove the "deleted" records from your dummy/temp table once you're done. Otherwise, they will show up every time.
>> EDIT << You can do self join to figure out if you're missing ids....
select a.id + 1 MissingIds
from <table> a
left join <table> b
on a.id = b.id - 1
where b.id is null
and a.id < 10000
Problem
You have a table `table1` that contains `id` column, that is `int(11), not null, auto_increment` and starts from 1. Suppose, you have 10,000 records. It is clear the id of the last record is 10,000. Once you removed 3 records, you have 9,997 records in the table, but the last record id value is still 10,000 (if the last record was not deleted). How to display what records have been removed using 1 sql query? Thank you.