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?

If you know it, and can get all `stand`'s in to its own array, you can do something like `$freeStands = array_diff(range(1,100), $idArray));` — Jon, Apr 02 '13 at 12:46
oh...outside the sql... i didn't think about it, thanks, it should be a solution — Matteo Bononi 'peorthyr', Apr 02 '13 at 12:46
` $taken_stands = array(1, 2, 3, 5, 7); echo $ids = explode(",", $taken_stands); $sql = "... WHERE STANDS NOT IN ($ids) ";` — , Apr 02 '13 at 12:47
@Akam that will only work if there are rows in the table that have `stands` column available but empty (or, not in the list defined). Though, it won't help if the data is not in the DB to begin with. — Jon, Apr 02 '13 at 12:49
you are obviuosly right, but i have no access on the db structure, too simple :D by the way, yes, it is the right approach, and in this particular case, the right one is to get out of the mysql, as Jon says at the beginning — Matteo Bononi 'peorthyr', Apr 02 '13 at 13:17

score 2 · Accepted Answer · answered Apr 02 '13 at 13:07

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.

score 1 · Answer 2 · answered Apr 02 '13 at 13:11

You can use a table of integers to get a range of numbers.

Say:-

SELECT a.i + b.i * 10 + c.i * 100 FROM integers a, integers b, integers c

With a table called integers with a single column called i, with 10 rows with the values 0 to 9 that will get you all the numbers between 0 and 999 (you can easily join against the table a few more times to get larger numbers).

You could join that against you table to find the unused numbers:-

SELECT *
FROM (SELECT a.i + b.i * 10 + c.i * 100 AS anInt FROM integers a, integers b, integers c) Sub1
LEFT OUTER JOIN someTable
ON Sub1.anInt = someTable.stand
WHERE someTable.stand IS NULL

This will work, but will give you all the numbers either end of the range in stand as well. for these you need to join against the min and max

SELECT Sub1.anInt
FROM (SELECT a.i + b.i * 10 + c.i * 100 AS anInt FROM integers a, integers b, integers c) Sub1
INNER JOIN (SELECT MIN(stand) AS MinStand, MAX(stand) AS MaxStand FROM someTable) Sub2
ON Sub1.anInt >= Sub2.MinStand AND Sub1.anInt <= Sub2.MaxStand
LEFT OUTER JOIN someTable
ON Sub1.anInt = someTable.stand
WHERE someTable.stand IS NULL

score 1 · Answer 3 · answered Apr 02 '13 at 13:33

A simpler solution, in my opinion, would be to have a table with all the number between one and 100. Then do a left join from that table to the stands table and show any values that don't match.

You could create a table called "integer_values" with a field called "value". Then populate it with all the numbers from 1 to 100. Then you could use the following query:

SELECT  i.value

FROM    integer_values AS i

        LEFT JOIN stands AS s
        ON i.value = s.stand

WHERE   s.stand IS NULL

this could be the right approach, but i have no access to the db structure, so I could not alter any table :/ — Matteo Bononi 'peorthyr', Apr 03 '13 at 09:27

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

3 Answers3