CODEWITHSUNDEEP
sqlms-access
panny
panny

Reputation: 2212

Show deleted Auto-Increment rows

I created a table with just integers, called Integers, with entries from 1 to 74 to find out which Autoincrement IDs have been deleted from the table ATO, which also has 74 rows

SELECT Integers.ID
  FROM Integers
  LEFT JOIN ATO
    ON Integers.ID = ATO.ID
 WHERE ATO.ID IS NULL

Is there an internal SQL range table I could have used, something like Range[1-74] so I can spare creating this "useless" Integers table? Something like:

SELECT Range [1-74].ID
  FROM Integers
  LEFT JOIN ATO
    ON Range [1-74].ID = ATO.ID
 WHERE ATO.ID IS NULL

I'm testing my design with Microsoft Access because it's quick and dirty and easy to fire SQL queries upon. I will port later to MySQL.

Upvotes: 1

Views: 131

Answers (2)

HansUp
HansUp

Reputation: 97131

Access SQL does not support what you want. If you're highly motivated to eliminate the Integers table, you could substitute a UNION query.

SELECT 1 AS the_number
FROM Dual
UNION ALL
SELECT 2
FROM Dual
UNION ALL
... etc, up to ...
SELECT 74
FROM Dual

Note Dual is a custom table designed to include only one row.

However I'm not sure how many SELECTs you can UNION together. If 74 is possible, it would probably be slow.

With Access SQL, the Integers table is easier.

If you can use something other that SQL, you could create a VBA procedure to examine the ATO table and identify which of your range values are missing from the table.

Upvotes: 1

Frank Goortani
Frank Goortani

Reputation: 1433

you can use the where clause

SELECT Integers.ID from Integers left join ATO on Integers.ID=ATO.ID where ATO.ID is null 
AND Integers.ID BETWEEN 1 AND 74

better yet, use the IN keyword

SELECT ID from Integers WHERE ID BETWEEN 1 AND 74 
AND Integers.ID NOT IN (SELECT ID FROM ATO)

Upvotes: 0

Related Questions