left join not working

Question

I am trying to show the free rooms in my hotel by start date and end date

enter image description here

When I use This code it only shows me the room wich appear in reservation but it doeen't shows me the room which were never reserved.

SELECT room.RoomID,room.Room_Type_ID,room.Room_number,room.NumberofSpots,
 res.Data_Check_in,res.Data_Check_out FROM    

      dbo.Reservation res JOIN dbo.Room room ON room.RoomID=res.Room_ID
      WHERE NOT(res.Data_Check_in<=@p_StartData AND res.Data_Check_out>=@p_EndData)

When i use this code its shows me all the rooms even those reserved:

SELECT DISTINCT r.*
FROM dbo.Room r LEFT JOIN dbo.Reservation  res ON r.RoomID=res.Room_ID
AND  NOT(res.Data_Check_in<='2012-05-07' AND res.Data_Check_out>='2012-06-13')
AND res.Cancel=0

What should i modify to get all the rooms without those which are reserved for selected date?

That's nice. Perhaps if you asked a question you might get better results. — Erik Funkenbusch, May 15 '12 at 18:58
Probably some issue with ( ), etc. First off you need a left join not an inner join. Secondly wrap the entire condition in a ( ). — JonH, May 15 '12 at 19:04
If someone checks out on a given date, can someone else check in on the same date? — HABO, May 15 '12 at 21:10

score 2 · Accepted Answer · answered May 15 '12 at 22:33

Please excuse the use of Date rather than Data in variable and column names.

declare @p_StartDate as Date = '2012-05-07'
declare @p_EndDate as Date = '2012-06-13'

declare @Room as Table ( Room_ID Int Identity, Room_number VarChar(5) )
insert into @Room ( Room_number ) values
  ( '101' ), ( '102' ), ( '103' ), ( '104' ), ( '105' ),
  ( '201' ), ( '202' ), ( '203' ), ( '204' ), ( '205' )

declare @Reservation as Table ( ReservationID Int Identity, Room_ID Int, Date_Check_in Date, Date_Check_out Date, Cancel Bit )
insert into @Reservation ( Room_ID, Date_Check_in, Date_Check_out, Cancel ) values
  ( 3, '2012-05-01', '2012-05-06', 0 ), -- Before.
  ( 3, '2012-06-14', '2012-07-01', 0 ), -- After.
  ( 4, '2012-05-07', '2012-06-13', 0 ), -- Matching.
  ( 5, '2012-06-01', '2012-06-05', 0 ), -- Within.
  ( 6, '2012-05-01', '2012-06-01', 0 ), -- Overlapping start.
  ( 7, '2012-06-01', '2012-06-15', 0 ), -- Overlapping end.
  ( 8, '2012-06-01', '2012-06-05', 1 ), -- Within, but cancelled.
  ( 9, '2012-06-01', '2012-06-15', 1 ), -- Overlapping, but cancelled.
  ( 10, '2012-01-01', '2012-12-31', 0 ) -- Containing.

select room.Room_ID, room.Room_number
  from @Room as room
  where not exists (
    select 42
      from @Reservation
      where Room_ID = room.Room_ID and Cancel = 0 and
        -- The date ranges overlap.
        ( ( ( @p_StartDate <= Date_Check_in ) and ( Date_Check_in <= @p_EndDate ) or
        ( @p_StartDate <= Date_Check_out ) and ( Date_Check_out <= @p_EndDate ) ) or
        -- The desired range is contained in the reserved range.
        ( ( Date_Check_in <= @p_StartDate ) and ( @p_EndDate <= Date_Check_out ) ) ) )

+1 This is great except range overlap detection can be much simpler. Please take a look at [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap). — Nikola Markovinović, May 15 '12 at 22:55
@NikolaMarkovinović - Quite so! I'll leave it as an exercise for the reader. — HABO, May 15 '12 at 23:32

Julian · Answer 2 · 2012-05-15T21:29:10.047

0

How about

SELECT Dr.* FROM dbo.Room r LEFT JOIN dbo.Reservation res 
ON r.RoomID=res.Room_ID
WHERE res.Room_ID IS NULL

For rooms never reserved.

A left join won't help with rooms reserved but not today. For that you want something like

SELECT Room_ID FROM Reservation WHERE Data_Check_out<=? OR Data_Check_in>=?
OR (Data_Check_out<=? AND Data_Check_in<=? AND Cancel=1 )

Some good comments. We know the left join will give us "never used rooms".

Without the cancel field the following should work:

set @checkindate = '2012-05-15';

set @checkoutdate = '2012-05-17';

select room_id from reservation where not (@checkindate between Data_Check_in and Data_Check_out OR @checkoutdate between Data_Check_in and Data_Check_out)

But the cancel makes things much harder as we need to know that all the days wanted are available. This feels more like a set operation on individual days is wanted.

edited May 15 '12 at 21:29

answered May 15 '12 at 19:08

Julian

1,522
11
26

But you can't select a room by date if there is no reservation! If there is no reservation, there is no reservation on the day you are interested in, so thats OK. For the rest, the second query is more appropriate. You can combine the two, – Julian May 15 '12 at 19:24
The task at hand appears to be identifying rooms for which there is no reservation over a specified period, e.g. someone wants a room for all of next week, which rooms are available then? The nuisance is detecting overlapping date ranges between the requested period and the outstanding reservations. – HABO May 15 '12 at 21:04
Yes but also want to see rooms that vere never reserved – jonny May 15 '12 at 21:16

left join not working

2 Answers2