create dummy rows between two numbers in mysql

Question

i am implementing project in php where user have some books, with series number for example Book No.1 - series 151 to 175, that means book no.1 have 25 pages each pages have unique series no. from 151, 152... 175.

user uses one page of specific book for generating some type of voucher. sometimes two voucher can be generated for single page i.e. 151A & 151B.

so i need a sql query which return dummy rows from 1 to 175, and left joins with voucher table

just like this

Answer 1

If your mysql version support CTE, you can try to use CTE RECURSIVE create a result set be the Outer JOIN table.

Or you can use WHILE with temp table to create a result set for series numbers.

Query #1

WITH RECURSIVE CTE AS (
    SELECT start_series,end_series
    FROM books
    UNION ALL
    SELECT start_series + 1,end_series
    FROM CTE
    WHERE start_series < end_series
)
SELECT c.start_series,
       coalesce(CONCAT(c.start_series,v.post_fix),v.voucher_page_no) voucher_no
FROM CTE c LEFT JOIN voucher v 
on c.start_series = v.voucher_page_no
ORDER BY c.start_series;

| start_series | voucher_no |
| ------------ | ---------- |
| 151          | 151A       |
| 151          | 151B       |
| 152          | 152        |
| 153          | 153        |
| 154          |            |
| 155          | 155        |
| 156          |            |
| 157          |            |
| 158          |            |
| 159          |            |
| 160          |            |
| 161          |            |
| 162          |            |
| 163          |            |
| 164          |            |
| 165          |            |
| 166          |            |
| 167          |            |
| 168          |            |
| 169          |            |
| 170          |            |
| 171          |            |
| 172          |            |
| 173          |            |
| 174          |            |
| 175          |            |

---

View on DB Fiddle