Is it possible to loop SQL according to data

I have a table that has the beginning value of a range and the end value of a range. I want to populate another table based on the ranges of each row.
For Example:
TableWithRanges (Column1=StartRange, Column2=EndRange, Column3=Data)
Row 1 - (1,5,T)
Row 2 - (1,5,L)
Row 3 - (6,7,P)

Then the sql would result:
ImportTable(Column1=CurrentRangeNumber, Column2=Data)
Row 1 - (1,T)
Row 1 - (2,T)
Row 1 - (3,T)
Row 1 - (4,T)
Row 1 - (5,T)
Row 1 - (1,L)
Row 1 - (2,L)… so on and so forth

So far I figured out that I can do this with SQL

INSERT INTO ImportTable 
(
`Data`,
`CurrentRangeNumber`
)
SELECT 
`TableWithRanges`.`Data`,

//// <-but this part needs to loop through each row - > 

FROM `Fan`

WOW THAT IS AMAZING!!!, Awesome as always r937. I’ve never seen that before, I wish you knew how awe stricken I am. Is there any documentation on how that the + n works, because I have never seen that before? It took me a while to figure it out but I understand it pretty clearly. I did some test and found that I had to make an adjustment:

INSERT
  INTO ImportTable
     ( CurrentRangeNumber
     , Data )
SELECT [COLOR="red"]([/COLOR]TableWithRanges.StartRange [COLOR="Red"]-1)[/COLOR] + n
     , TableWithRanges.Data
  FROM TableWithRanges
INNER
  JOIN numbers
    ON TableWithRanges.StartRange + n
       BETWEEN TableWithRanges.StartRange
           AND [COLOR="red"]([/COLOR]TableWithRanges.EndRange [COLOR="red"]+1)[/COLOR]

It seems that the query starts off right off the bat on the number in the start range and adds 1 to it immediately so if the range is 1 - 10 the results will only create something for 2 - 10. And if you don’t add a +1 in the endrange in the where then it will only iterate for the number of rows that you have and you need just one more iteration to occur. I am amazed again.

It’s beautiful

okay, that’s quite manageable

could you create this table please –

CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ) ;
INSERT INTO numbers VALUES (0),(1),(2),...,(99) ;

then you can simply do this –

INSERT
  INTO ImportTable
     ( CurrentRangeNumber
     , Data )
SELECT TableWithRanges.StartRange + n
     , TableWithRanges.Data
  FROM TableWithRanges
INNER
  JOIN numbers
    ON TableWithRanges.StartRange + n
       BETWEEN TableWithRanges.StartRange
           AND TableWithRanges.EndRange

looping is ~not~ necessary

what is the largest range of start/end numbers you want to generate?

yes, it’s possible, and without a loop, too

what is the largest range of start/end numbers you want to generate?

AFAIK it isn’t possible, but your question raises another question: why do you need to do this? Most of the time when you “need” to create redundant data (data that is already in the db, but in a different form) it means you’re doing something wrong. The only exception being you need it because you have a huge (100,000+ rows) table and the performance is taking a hit …

<smacks head> Ohhhh, that is where the mysterious n comes from, the table! haha, that part went over my head :smiley:

I think instead of inserting that data into a separate table I’ll simply create a view that does this display. It will be easier to manipulate because if there is an update in the range table they will work, although I’ll always need to make sure that start < end.
Is there a way to lock in that condition into the mysql table schema like how a primary key has to be unique?

Even maybe the ability to make it so that no start - end overlap another start - end (For this I’m thinking a delete table and a Distinct Query is easier)

hmmm, that’s strange

it should start off with n=0… are you sure you added 0 to your numbers table?

but i’m glad you played with it, because of course that is the best way to learn how to write joins like this

and you have to admit, this particular ON clause is awesome, isn’t it

:slight_smile:

Well generally the section number ranges are from 101 - 135, 301 - 335, and 401-435

each section has a row range of 1 - 28

and each row has a seat range of 1-33

woo, lol are you ready for a long winded explanation haha…

…your question raises another question: why do you need to do this? Most of the time when you “need” to create redundant data (data that is already in the db, but in a different form) it means you’re doing something wrong. The only exception being …

what is the largest range of start/end numbers you want to generate?

The reason I want to do this is because we have an inventory of tickets that we sell. Each ticket has a Section [101 - 435] A Row [1 - ??] and a seat number [1 - ??]

So the database needs to keep track of all the tickets we have in office (because we only have a certain number (500 tickets give or take))

The reason for the looping is because now we get a table that has the information where each row of the table equates to a section, and a column that says what the first seat and last seat of the stack is.

So in order to keep track of who buys what ticket I was thinking of treating each ticket as an inventory item, each seat unique to another.
Looping is essentially to save to time of the person who would normally make a list of all the tickets available the time needed to enter each of them into the database.

The issues that come up is that sometimes we will get new tickets, and other times we might get an update on our tickets where we have to give a few of them back, or they give us some back, — which is where I begin to have nightmares. lol