mysql query where IN statement

Question

Hi there. I want to do the following:

SELECT count(id) FROM table WHERE value BETWEEN 3 AND 40;

But it should do the following:

SELECT count(id) FROM table WHERE value IN(3, 4, 5, 6, 7, 8, 9, 10, 11, ..., 40);

It should even print out zero count(id) for value between 3 and 40, but not value = x. I want to check if a value is in a sequence (1, 2, 3, 4, ..., 50).

Does anyone know how to achieve this with mysql?

Thanks.

Answer 1

Assuming I understood your question:

SELECT count(id) FROM table WHERE value >= 3 AND value <= 40 AND value != 'x'

Edit: I think I know what you mean

SELECT COALESCE(count(id),0)  FROM table WHERE value BETWEEN 3 AND 40;

Answer 2

Here is a guess at what you are looking for:

select c.Value,  count(t.Value) as Count
from (
    select 3 as Value
    union all select 4
    union all select 5 
    union all select 6 
    union all select 7 
    union all select 8 
    union all select 9 --add more as needed
) c
left outer join MyTable t on c.Value = t.Value
group by c.Value

Answer 3

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. This will return a list of the values you want to see counts for:

   SELECT n.id
     FROM NUMBERS n
    WHERE n.id BETWEEN 3 AND (? - 1)
       OR n.id BETWEEN (? + 1) AND 40
   

4. LEFT JOIN onto your existing table to be able to see where the COUNT is zero:

     SELECT x.id AS value,
            COALESCE(COUNT(y.id), 0) AS cnt
       FROM (SELECT n.id
               FROM NUMBERS n
              WHERE n.id BETWEEN 3 AND (? - 1)
                 OR n.id BETWEEN (? + 1) AND 40) x
   LEFT JOIN YOUR_TABLE yt ON yt.value = x.id
    GROUP BY x.id