CODEWITHSUNDEEP
mysql
scholzr
scholzr

Reputation: 245

multiple rows in query from one row MySQL

I have a MySQL table as below. I want to create a query to return multiple rows for each entry based on the count of "seed" as in my example. Is this possible on MySQL?

Table:

-------------------------------
| ID      | Name    | Seed    |
| 1       | Entry 1 | 2       |
| 2       | Entry 2 | 3       |
| 3       | Entry 3 | 1       |
| 4       | Entry 4 | 2       |
-------------------------------

Results:

ID    Name     Seed
1     Entry 1  2
1     Entry 1  2
2     Entry 2  3
2     Entry 2  3
2     Entry 2  3
3     Entry 3  1
2     Entry 2  2
2     Entry   2

Upvotes: 3

Views: 168

Answers (2)

Justin
Justin

Reputation: 9724

When you need multiple rows, you need integer values by which you gonna multiple your rows. So my example will be with Subquery which holds integer values.

Query:

SQLFIDDLEExample

SELECT t.ID, t.Name, t.Seed
FROM Table1 t
LEFT JOIN (SELECT
    (TWO_1.SeqValue + 
     TWO_2.SeqValue + 
     TWO_4.SeqValue + 
     TWO_8.SeqValue + 
     TWO_16.SeqValue) SeqValue
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
)t2
ON t2.SeqValue < t.SEED

Result:

| ID |    NAME | SEED |
-----------------------
|  1 | Entry 1 |    2 |
|  1 | Entry 1 |    2 |
|  2 | Entry 2 |    3 |
|  2 | Entry 2 |    3 |
|  2 | Entry 2 |    3 |
|  3 | Entry 3 |    1 |
|  4 | Entry 4 |    2 |
|  4 | Entry 4 |    2 |

Upvotes: 0

Craig Neil Brown
Craig Neil Brown

Reputation: 61

You can introduce another table of sequential seed values. Then your query can join to it using >=.

Upvotes: 1

Related Questions