Skip to content
Advertisement

Iterate rows base on its entries column value

I need help on SQL SELECT QUERY.

This is my TABLE.

id | name             | entries |
---------------------------------
1  | Jose Rizal       | 1       |
2  | Raffy Tulfo      | 2       |
3  | Cong TV          | 1       |
---------------------------------

I want to select get them like this

| name             |
--------------------
| Jose Rizal       |
| Raffy Tulfo      |
| Raffy Tulfo      |
| Cong TV          |
--------------------

If you can notice, Raffy Tulfo has 2 rows because it has 2 entries.

`

Advertisement

Answer

We can use cte to achieve this requirement if your mysql is version 8.0 up. but if its below, 1 way to do this is using dummy values using union.

SELECT name
FROM test t1
    JOIN (
                SELECT 1 as number UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 
         ) n
    ON t1.entries >= n.number
order by name;

see dbfiddle

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement