Question 112812 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Fill up rows to count

I got the result of a query, and want to join it with a mockup table to fill the rows up to a certain number. this is an example where the query has two rows as result, but i always want that to fill it up to 5 (see column slot)

SELECT * FROM foo WHERE …
foo bar
jhkl jol
das das

result:

slot foo bar
1 jhkl jol
2 das das
3 NULL NULL
4 NULL NULL
5 NULL NULL

You help is highly appreciated!

Tags:

2

Answers


  1. 0

    You can use a recursive CTE to produce exactly 5 rows every time. For example:

    with recursive g (n) as (select 1 union all select n + 1 from g where n < 5)
select g.n as slot, x.foo, x.bar
from g
left join (select t.*, row_number() over() as rn from t) x on x.rn = g.n

    Result:

     slot  foo   bar  
 ----- ----- ---- 
 1     abc   def  
 2     ghi   jkl  
 3     null  null 
 4     null  null 
 5     null  null

    See running example at db<>fiddle.

    Login or Signup to reply.
  2. 0

    This should work on MySQL 5.7:

    select s.slot, f.foo, f.bar
from 
(select @row:=0) as _init
cross join (
  select 1 as slot union select 2 union select 3 union select 4 union select 5
) as s
left outer join
(
  select @row:=@row+1 as slot, foo, bar from foo
) as f using (slot);

    Output:

    +------+------+------+
| slot | foo  | bar  |
+------+------+------+
|    1 | jhkl | jol  |
|    2 | das  | das  |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
|    5 | NULL | NULL |
+------+------+------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search