# Shantanu's Blog

Corporate Consultant

## MySQL Case Study - 13

Integer Tables explained:

Here's what I need to do...

create table wibble(
seq int(3) auto_increment primary key,
x int(5), y int(5));

insert into wibble set x=5, y=10;
insert into wibble set x=1, y=3;
insert into wibble set x=17, y=22;

mysql> select * from wibble;
+-----+------+------+
| seq | x | y |
+-----+------+------+
| 1 | 5 | 10 |
| 2 | 1 | 3 |
| 3 | 17 | 22 |
+-----+------+------+
3 rows in set (0.09 sec)

So I want to run a query to explode the x/y ranges by seq.

The required output is:

+-----+------+
| seq | z |
+-----+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 17 |
| 3 | 18 |
| 3 | 19 |
| 3 | 20 |
| 3 | 21 |
| 3 | 22 |
+-----+------+
14 rows in set (0.17 sec)

Can anyone help me to achieve this result?

In SQL you need to define the data that you want to work with:

create table z ( z int(5) not null primary key);
insert into z values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22);

If you need more values you could use a simple perl looping construct:

for (\$i=0; \$i <= \$max; \$i++) {
\$dbh->do( q{ insert into z set z = \$i });
}

Once you have the table filled, it's easy to explode the x/y ranges by seq:

select seq, z
from wibble, z
where z between x and y

Then the rows with seq=1 are:
| seq | z |
+-----+----+
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
The rest is as you wanted.