Selecting only those numbers that are in array AND not in a table

Question

I have a table in MySql where I save some data let's assume a name and a stand. I know that the stands will be from 1 to 100, i would like to select those stands that aren't taken. For example let's assume that whe have only 5 stands and this table:

|  name  |  stand  |
--------------------
|  test  |    1    |
|  anot  |    3    |
|  blah  |    4    |
|  uuuh  |    5    |

in this case the only free stand will be the 2.

is there a statement to do that ? ... I was thinking at the clause NOT IN but I can't figure out the code... maybe if I can define am Array in MySql?

Answer 1

If you know the values are from 1 to 100, then you can do this:

select n.num
from (select d1.d*10 + d2.d as n
      from (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
            select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) d1 cross join
           (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
            select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) d2
      ) nums left outer join
      stands s
      on s.stand = nums.n cross join
      (select min(stand) as minstand and max(stand) as maxstand from stands) const
where s.stand is null and nums.n between minstand and maxstand;

This is not tested, so it may have syntax errors.

That is, create a table with all possible values (1 to 100). Left join this to your table. This gives you all the numbers that are not used. However, you want to limit it to the min and max values, so calcualte these and use them for filterin.