drop table bookings purge
create table bookings ( hr int, room varchar2(10), who varchar2(10))
insert into bookings values( 8, 'Room2' , 'PETE')
insert into bookings values( 9, 'Room1' , 'JOHN')
insert into bookings values( 11, 'Room1' , 'MIKE')
insert into bookings values( 14, 'Room2' , 'JILL')
insert into bookings values( 15, 'Room2' , 'JANE')
insert into bookings values( 16, 'Room1' , 'SAM')
drop table hrs purge
create table hrs ( hr int )
insert into hrs values ( 8)
insert into hrs values ( 9)
insert into hrs values (10)
insert into hrs values (11)
insert into hrs values (12)
insert into hrs values (13)
insert into hrs values (14)
insert into hrs values (15)
insert into hrs values (16)
select * from bookings
HR | ROOM | WHO | 8 | Room2 | PETE | 9 | Room1 | JOHN | 11 | Room1 | MIKE | 14 | Room2 | JILL | 15 | Room2 | JANE | 16 | Room1 | SAM |
---|
select * from hrs
HR | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
---|
select hrs.hr, t1.room, t1.who
from hrs, bookings t1
where hrs.hr = t1.hr(+)
order by 1
HR | ROOM | WHO | 8 | Room2 | PETE | 9 | Room1 | JOHN | 10 | - | - | 11 | Room1 | MIKE | 12 | - | - | 13 | - | - | 14 | Room2 | JILL | 15 | Room2 | JANE | 16 | Room1 | SAM |
---|
select hrs.hr, t1.room, t1.who
from bookings t1
partition by (t1.room)
right outer join hrs on (hrs.hr = t1.hr)
order by 1,2
HR | ROOM | WHO | 8 | Room1 | - | 8 | Room2 | PETE | 9 | Room1 | JOHN | 9 | Room2 | - | 10 | Room1 | - | 10 | Room2 | - | 11 | Room1 | MIKE | 11 | Room2 | - | 12 | Room1 | - | 12 | Room2 | - | 13 | Room1 | - | 13 | Room2 | - | 14 | Room1 | - | 14 | Room2 | JILL | 15 | Room1 | - | 15 | Room2 | JANE | 16 | Room1 | SAM | 16 | Room2 | - |
---|