sql server - Select from sql table, fill in non-existing entries -

i have 2 tables:

rooms{ roomnum, maxoccupancy}  reservations{name, roomnum} 

i want select of names reservations given room number, if number of rows returned less maxoccupancy room number, want return null (or "empty") rows empty spots.

so know need start with:

select name reservations reservations.roomnum=7 , reservations.roomnum = rooms.roomnum 

but there's whole lot more done.

edit

a sample dataset be:

rooms: roomnum, max occupancy:          7    , 4  reservations: name, roomnum:               me,    7                you,   7 

so result be:

result: name:         me                 null (or "empty", need row exist)         null 

assuming sql server, following query useful:

create table reservations(   name varchar(100),   roomnum int   )  create table rooms(   roomnum int,   maxslots int   )  insert rooms values(7,4) insert reservations values('me',7) insert reservations values('you',7)  -- here query!! temp ( n ) (     select 1 union     select 1 + n temp n < 100 )  select t.n slot, reserv.name, a.roomnum rooms inner join temp t on t.n <= a.maxslots left join (   select r.name, row_number() over(order r.name) slotnumber   reservations r   ) reserv on reserv.slotnumber = t.n 

you can try here.

note: careful should be, n < 100.

(edited 2013-07-14) here solution little more generic, works wiht more 1 room reservation:

with temp1 ( n ) (   select 1 union   select 1 + n temp1 n < 100 ), temp2 (   select    x.roomnum,   x.name,   dense_rank() on (partition x.roomnum order x.roomnum,x.name) rownum   reservations x )  select a.roomnum, t1.n slot, t2.name rooms inner join temp1 t1 on t1.n <= a.maxslots left join temp2 t2 on t2.rownum = t1.n , a.roomnum = t2.roomnum order a.roomnum,t1.n,t2.name 

you can try here.