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.

I would be sorely tempted to handle this kind of thing at the application level - e.g. with a simple PHP loop. — Strawberry, Mar 26 '13 at 14:17

Simon at The Access Group · Accepted Answer · 2013-03-26T14:16:52.717

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.

edited Mar 26 '13 at 14:16

answered Mar 26 '13 at 13:06

Simon at The Access Group

6,932
22
45

+1 . . . But I would replace the `union` with `union all` since duplicate elimination is not needed. – Gordon Linoff Mar 26 '13 at 14:12
Agreed @Strawberry, a PHP solution would be better. This is just a short term solution. – Derek Mar 26 '13 at 17:30
@Simonatmso.net - so simple, so fast! And there is a size table so in line with your suggestion I have amended the join to 'INNER JOIN sizes ON sizes.size_id BETWEEN yt.minsize_id AND yt.maxsize_id'. Thank you! – Derek Mar 26 '13 at 17:34

score 0 · Answer 2 · edited May 23 '17 at 12:23

Here's one way:

SELECT id, style, size_id
FROM origTable ot
JOIN 
(SELECT @rownum := @rownum + 1 AS size_id
 FROM origTable ot1
 CROSS JOIN origTable ot2,
 (SELECT @rownum := -1) r) s
ON ot.minsize_id <= s.size_id AND ot.maxsize_id >= s.size_id
ORDER BY id, style, size_id

It may look a little messy at first glance but I'll try and explain:

MySQL doesn't have a row_number function like SQL Server does so I'm using a technique described here to artificially create this in the subquery (s). To create a lot of row numbers there needs to be a lot of rows in this so you could either choose a different table that already has a lot of rows or as I've done just do a CROSS JOIN of your original table - CROSS JOINs generally ends up with a lot of rows. Bit of a hack I know and may have degrading performance if origTable becomes large!

We need the row numbers to start at zero and what is selected by the subquery is pre-incremented by 1, hence the initialisation of @rownum to -1.

See SQL Fiddle Demo.

Thanks for your ingenious answer, this also did the trick albeit with a lower performance as you suspected. — Derek, Mar 26 '13 at 17:40
Pleasure. The advantages this offers over the other answer is compactness of the query and flexibility if the number of size_ids becomes large. If performance is an issue you could kind of combine the two answers and join on your own table with n rows where n is the maximum difference between minsize_id and maxsize_id. — Steve Chambers, Mar 26 '13 at 22:00

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

2 Answers2