sql - Oracle union giving error -

i trying create view in oracle show rooms available in hotel not booked out.

if booked out there record in roombasket. trying select rooms not in roombasket between checkin , checkout dates (roombasket.datein, roombasket.dateout)

but query kept returning 0 results when used inner joins , outer (left/right) joins, think it's because not joined on table of dates have 0 results. trying union room table has record of rooms, think select rooms , negate ones booked out?

i can't syntax correct , played around loads of forms of query:

create view availability     (select * roombasket rb         to_date(sysdate, 'yyyymmdd')          not between to_date(rb.datein, 'yyyymmdd') , to_date(rb.dateout, 'yyyymmdd')         union (select r.id room, rt.type type, rt.price price room r, roomtype rt)     ); 

but if works 0 results , if doesn't work syntax errors. @ moment error is:

query block has incorrect number of result columns

you have no predicate on join of room roomtype, cross join. unlikely want.

furthermore, union looks it's trying add unrelated data room data. description of problem suggests want use roombasket data filter other data instead -- requires join or subquery.

something more along these lines want:

create view availability (   select r.id room, rt.type type, rt.price price       room r     inner join roomtype rt       on rt.id = r.type     left join roombasket rb       on rb.room = r.id         , to_date(sysdate, 'yyyymmdd') between to_date(rb.datein, 'yyyymmdd')           , to_date(rb.dateout, 'yyyymmdd')   rb.room null ); 

the where predicate has effect of selecting rows of left table (room join roomtype) not matched row of right table (roombasket).