Take values that were not used within the bd

Asked

Viewed 199 times

1

I have a table with several product codes:

EX: 1, 2, 3 ,4 , 5, 15, 20, 21, 22, 45, 60...

I wonder if there is a query to get the codes that have not been used in the records? In the above example code 5 jump to code 15, in which case I would like to take code 6, 7, 8, 9, 10, and so on.

mysql select-sql

  • Vitor, I advise you to include more details. Such as: details of your table, when setting up a ID of these was used?? It is inserted in another table? How does it work? Then we can help you. If they are in separate tables is quite simple, just use a NOT EXISTS or EXISTS depends on where the query will start. Example SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

  • @ Fernando A.W. , These records are entered manually, which caused this mess in the code to skip some at the time of registration.

  • but they stay on the same table? I guess I got it, due to being insert manual you want to take the values that were not used.

  • Correct Fernando A.W., get the records within only one table!

  • Vitor, from a look at the answer I put, should meet your need.

1 answer

5


Vitor, I’d do it this way.

Would take the amount: MAX() from your table. Let’s say 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 precedent to feed this table with the range of data you want to search.

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 ;

Then we run the trial:

CALL dowhile();
SELECT * FROM incr;

Upshot:

Id
1
2
3
...
100

After that we consult using the NOT EXISTSto take 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 non-existent codes in your table.

  • Fernando, it worked perfectly, thanks for the help

  • @Vitor. Something screams in there. I only ask you to solve the problem, mark as "answer", thus facilitates for the staff, in possible searches.

  • Thank you! gave it right! Thanks for the tip.

Browser other questions tagged mysql select-sql

You are not signed in. Login or sign up in order to post.