Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I find in a column filled with numbers, the ones that are not used.

Table_A:

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,        
'client_code' int(4) unsigned NOT NULL,    
'client_name' varchar(50) NOT NULL,    
PRIMARY KEY ('id')

INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');    
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');    
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');    
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');    
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

If I SELECT 'client_code' FROM Table_A the output will be 1,2,5,6,10. How can I have the numbers that are not used?

(3,4,7,8,9,11,12...)
share|improve this question
    
upto which number you need? –  Praveen Prasannan Aug 24 '13 at 8:47
add comment

3 Answers

up vote 2 down vote accepted

To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.

Helper table to provide numbers from 0-9:

CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Building on that, generate a complete range of numbers:

SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

Now you can generate the list of missing numbers, exactly as requested:

SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:

SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

-> SQLfiddle demo.

share|improve this answer
add comment

Create a stored procedure as below

DELIMITER $$
CREATE PROCEDURE CheckNotUsedNos(in v1 int)
BEGIN
DROP TEMPORARY TABLE IF EXISTS `incr`;
CREATE TEMPORARY TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
);
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;

Select Id from `incr` 
where Id not in 
(select client_code from `Client`);

END $$
DELIMITER;

Call as you would like to have your parameter. (Just trying with 11 )

call CheckNotUsedNos(11);

And I got below results

ID
3
4
7
8
9
11

SAMPLE FIDDLE

share|improve this answer
add comment

You can have it by the difference between "rownumber" generated for all the rows in the Table_A and client_code:

select * from (
  SELECT @ROWNUMBER:=@ROWNUMBER+1 AS COL FROM Table_A
  JOIN    (SELECT @ROWNUMBER := 0) D) B
where B.COL not in (select client_code from Table_A);

your client_code values have to start from 1 and increment by one.

share|improve this answer
1  
No, this will only return two rows with 3 and 4: SQL-Fiddle –  ypercube Aug 23 '13 at 19:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.