1,105,271 Community Members

stumped mysql query

Member Avatar
paul.vanzyl.313
Newbie Poster
1 post since Sep 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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.

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
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 three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: