One way would be to build a "count" table with one column starting with value 1 and incrementing by 1 up to say 500 rows or how many your max y value is. Then just select seq,val from wibble,count where val between x and y
create table count (val INT unsigned default '0' not null primary key) -----Original Message----- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 13, 2005 7:33 PM To: MySQL Subject: Re: Help with a tricky/impossible query... I should mention that I'm constrained to version 4.0.n so no sub queries for me! Andrew On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi, > > I need some help with a tricky query. Before anyone asks, I cannot bring > this functionality back to the application layer (as much as I'd like to). > > 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: > > mysql> select some clever things from wibble where some clever stuff happens > here; > +-----+------+ > | 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? > > Thanks, > > Andrew > > SQL, Query > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
