1.11M Members

stumped mysql query

 
0
 

I need to print labels. I have people who ordered more than one book so i have to print the amount of labels equal to the amount of books they ordered.
Below query.

select id,name,total_copies from contacts. 
that's the select statement. As is it can bring back

1,john,1
2,peter,3
3,sara,2
I need it to be

1,john,1
2,peter,2
2,peter,2
2,peter,2
3,sara,2
3,sara,2

total_copies column decides how many times to return the row.

 
0
 

Up to 99 copies you can use:

select id, name, total_copies 
from contacts
    inner join 
    (select a.id + b.id as copy_count
     from 
         (select 1 as id union all select 2 union all select 3
           union all select 4 union all select 5 union all select 6 union all select 7
           union all select 8 union all select 9) a 
     cross join 
         (select 0 as id union all select 10 union all select 20
          union all select 30 union all select 40 union all select 50
          union all select 60 union all select 70
           union all select 80 union all select 90) b) c 
on total_copies >= copy_count

After 99 copies you have to add another "virtual" table with hundrends and a zero, just like table b.

What this does is create a table with numbers from 1 to 99 and joins it to your contacts table.

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: