I have a table with multiple product codes:
EX: 1, 2, 3, 4, 5, 15, 20, 21, 22, 45, 60 ... 100
Would you like to know if there is any query to pick up the codes that have not yet been used in the records? In the above example code 5 jump to code 15, in that case I would like to get codes 6, 7, 8, 9, 10, and so on.
Vitor, I would do it as follows.
It would take the value: MAX() of your table. Let's say it's 100. Then it would generate a temporary table with values from 1 to 100.
CREATE TABLE 'incr' (
'Id' int(11) NOT NULL auto_increment,
PRIMARY KEY ('Id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Next a procedure to feed this table with the data range you want to fetch.
DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 100;
WHILE v1 > 0 DO
INSERT incr VALUES (NULL);
SET v1 = v1 - 1;
END WHILE;
END;;
DELIMITER ;
Next, we execute the procedure:
CALL dowhile();
SELECT * FROM incr;
Result:
Id
1
2
3
...
100
After this we query using NOT EXISTS to get the values that are not in your table:
SELECT DISTINCT ID FROM incr
WHERE NOT EXISTS (SELECT * FROM SUA_TABELA_AKI
WHERE SUA_TABELA_AKI.ID= incr.ID);
And this way you will have all the codes that do not exist in your table.