CODEWITHSUNDEEP
mysqlsqlselect
endo.anaconda
endo.anaconda

Reputation: 2478

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!

Upvotes: 0

Views: 49

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 563021

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 |
+------+------+------+

Upvotes: 1

The Impaler
The Impaler

Reputation: 48875

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.

Upvotes: 2

Related Questions