mysql select table where number of rows depends on dynamic values in two columns

Question

I have a MySQL table:

id | style  | minsize_id | maxsize_id
-------------------------------------
1  | Jacket | 1          |  3
2  | Pant   | 2          |  4
3  | Hoody  | 0          |  3

I would like to show an extra row for each size id between and including the minsize_id and maxsize_id (sizes can be any integer between 0 and 9), and a new column 'size_id' as follows:

id | style  | size_id
----------------------
1  | Jacket | 1
1  | Jacket | 2
1  | Jacket | 3
2  | Pant   | 2
2  | Pant   | 3
2  | Pant   | 4
3  | Hoody  | 0
3  | Hoody  | 1
3  | Hoody  | 2
3  | Hoody  | 3

How do I display the table in this way using only MySQL? Thanks for any help.

Answer 1

One way this could be achieved is to join this table onto a derived table which contains 10 rows, one for each size. For example

SELECT

    yt.id,
    yt.style,
    sizes.size_id

FROM yourTable AS yt

INNER JOIN (
    SELECT 0 AS size_id
    UNION ALL
    SELECT 1 AS size_id
    UNION ALL
    SELECT 2 AS size_id
    UNION ALL
    SELECT 3 AS size_id
    UNION ALL
    SELECT 4 AS size_id
    UNION ALL
    SELECT 5 AS size_id
    UNION ALL
    SELECT 6 AS size_id
    UNION ALL
    SELECT 7 AS size_id
    UNION ALL
    SELECT 8 AS size_id
    UNION ALL
    SELECT 9 AS size_id
) AS sizes
ON sizes.size_id BETWEEN yt.minsize_id AND yt.maxsize_id

If however you have a table "sizes" with primary key "size_id" then you can just INNER JOIN that instead of this derived table. Obviously replace yourTable and alias yt with whatever table name / alias you desire.