0

I have a SQL data like this:

id  date_start  date_end  identical_value
1   1           1         a
2   1           3         b
3   2           3         c

I need return multiple semi-identical results, but only with ID and a new column. Below an example:

id  date_reference  identical_value
1   1               a
2   1               b
2   2               b
2   3               b
3   2               c
3   3               c

Note that the column date_reference starts on date_start and end on date_end.

I have a good notion on basic SQL, but I don't undestand to much of loopings, and I think that maybe is essential here.

What I need: a good example -- not exactly the answer -- to I understand if it is possible or not, or what I need to search (functions, keyworkds...) of MySQL to do.

David Rodrigues
  • 12,041
  • 16
  • 62
  • 90

1 Answers1

1

Create a table with integers from 1 to some very large numbers. Then you can join on the number table to expand the date range:

select  yt.id
,       num.Number as date_reference
,       yt.identical_value
from    YourTable yt
join    NumberTable num
on      num.Number between yt.date_start and yt.date_end
Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks, but I can't use more than one table -- all useful data are on this table. And my example is hypotetical. For instance: date_start is DATE type, not a INT. I need to know how SQL loopings works for cases like that. – David Rodrigues Aug 06 '11 at 17:04
  • Ah!!! I think that I understand what you suggest. On my case, I need create a table with DATEs from TODAY to 10 YEARS AFTER, and loop this data, right? Thanks... I'll try and I will back with a result... – David Rodrigues Aug 06 '11 at 18:42