Re: need help forming sql query...

You just need to outer join the reservations table to your rooms table.

<mcharon_at_yahoo.com> wrote in message news:90eggk$l1l$1_at_nnrp1.deja.com...
> Hi Tom,
> Thank you for your suggestion.
> Is it possible to modify the sql statement so that rooms that have NOT
> been reserved will be returned from the query.
>
> The rooms that have not been reserved will NOT be in the
> 'room_reservations' table.
>
>
> For example, if room 22 has not been reserved at all, the querry still
> should return
> Room Total_hr_reserved
> 20 1
> 21 2
> 22 0 (NOTE: room 22 is not reserved)
>
> as opposed to
> Room Total_hrs_Reserved
> 20 1
> 22 2
>
> Again, hopefully all this can be done with one sql statment..
> Thank you in advance.
>
>
> In article <AVlW5.3072$tR6.567081_at_news.uswest.net>,
> "Tom Leary" <tleary1_at_uswest.net> wrote:
> > Just remember that the unit for date fields is days, so if
 date1=12/01/2000
> > 13:00, and date2=12/02/2000 19:00, then date2 - date1 = 1.25 (days).
 To
> > convert to another time format, like minutes, multiply by the
 conversion
> > factor: 1.25 days = 1.25 d * 24 h/d = 30 hours = 1.25 d * 24 h/d *
 60 m/h
> > = 1800 min
> >
> > So, your query for total minutes reserved for each room is
> >
> > select room_id "Room", sum( (end_time - start_time) * 24 * 60 )
> > "Total_Min_Reserved"
> > from room_reservations
> > group by room_id
> >
> > HTH,
> >
> > DBA Tom
> >
> > <mcharon_at_yahoo.com> wrote in message
 news:90cg1o$97d$1_at_nnrp1.deja.com...
> > > suppose i create two tables
> > >
> > > /*this table describes the room */
> > > creat room (
> > > room_id NUMBER not null/* this is the room number */
> > > )
> > >
> > > /*this table describes room reservations */
> > > create room_reservations (
> > > room_id NUMBER ; /* this is the room number */
> > > start_time DATE; /*the room is reserved from 'start_time' to
 'end_time'
> > > end_time DATE; /*the room is reserved from 'start_time' to
 'end_time'
> > > )
> > >
> > > How do I write sql statement to find out total hrs reserved for each
> > > room?? i am hoping to be able to do this withh one sql statement and
 by
> > > not adding anymore columns to the tables.
> > > if the query turns out to be a complicated sql statement, I would
> > > appreciate some sort of explanation as well, if it's not too much
> > > trouble.
> > >
> > > For example, if the row entries for room table are
> > > room_id
> > > 20
> > > 21
> > > 22
> > > And row entries for room_reservations table are
> > > room_id start_time end_time
> > > 20 12pm 1pm
> > > 21 8pm 9pm
> > > 21 10pm 11pm
> > >
> > > The desired sql query should return
> > > room_id total_hrs_reserved
> > > 20 1
> > > 21 2
> > > 22 0
> > >
> > >
> > > I am stuck on this for over a week now;; any help will be greatly
> > > appreciated.
> > >
> > > thank you in advance
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> --
> thank you in advance
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Dec 09 2000 - 04:33:24 CST