#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    16
    Rep Power
    0

    Query returns record which is not needed


    Hi All
    I am trying to write a Hotel Room Booking System using Java and MySQL.

    I want to select those records only which are available between a range of dates.
    Suppose 112 is book for 5 days say from 22/11/2012 to 27/11/2012, then this record of room no. 112 must not be diaplayed for these dates
    22/11/2012
    23/11/2012
    24/11/2012
    25/11/2012
    26/11/2012
    27/11/2012
    Whether I select date range from 24/11/2012 to 26/11/2012 or from 21/11/2012 to 25/11/2012 or from 25/11/2012 to 27/11/2012. I my sql query is not performing what I want
    SELECT roomdetail.room_no, room_type, room_bed, room_rate FROM roomdetail LEFT JOIN bookingtable ON roomdetail.room_no=bookingtable.room_no AND DATE(date_fro) >= 'strdtver1' AND DATE(date_to) <= 'strdtver2' WHERE bookingtable.room_no IS NULL;

    Above query displays the room 112 which is already booked if I select dates range given in example.
    Table structure -
    roomdetail
    Field Type Null Key Default Extra
    room_no varchar(3) NO PRI NULL
    room_type varchar(10) NO NULL
    room_rate int(4) NO NULL
    room_bed varchar(6) NO NULL

    bookingtable
    Field Type Null Key Default Extra
    book_id int(3) NO PRI None
    room_no varchar(3) YES NULL
    date_fro datetime YES NULL
    date_to datetime YES NULL
    no_of_day int(3) YES NULL
    I trying to do this by this way but not successfull

    I need guidance and support pls help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Are you able to echo the actual query that MySQL sees?

    And can you clarify what exactly it is that you're after? For instance, would you like to see available reservation periods between given dates, or only rooms that are completely available for the entire date range?

    It seems that one or other of date_to and no_of_days is redundant. And why is date_fro datetime? Do you rent rooms by the hour? ;-)
    Last edited by cafelatte; November 20th, 2012 at 04:35 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    16
    Rep Power
    0
    Originally Posted by cafelatte
    Are you able to echo the actual query that MySQL sees?

    And can you clarify what exactly it is that you're after? For instance, would you like to see available reservation periods between given dates, or only rooms that are completely available for the entire date range?

    It seems that one or other of date_to and no_of_days is redundant. And why is date_fro datetime? Do you rent rooms by the hour? ;-)
    Thank you for considerating my problem

    YES I can see only those rooms which are avaliable if I mention date 22/11/2012 to 27/11/2012
    But if I mention dates eg.
    from 24/11/2012 to 26/11/2012 or from 21/11/2012 to 25/11/2012 or from 25/11/2012 to 27/11/2012
    This display the Room No. 112 also which should not because it book from 22nd to 27th.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    This response doesn't address any of my questions and, without site of your actual data, is in any case meaningless!

    Consider providing proper DDLs AND corresponding desired result set so that we can replicate your problem.

IMN logo majestic logo threadwatch logo seochat tools logo