Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Problem in LEFT JOIN

    Hi Everybody

    I am trying to list records of those rooms which are not booked between two dates. I have 2 tables
    roomtable
    "Field","Type","Null","Key"
    "room_no","varchar(3)","NO","PRI"
    "room_type","varchar(10)","NO"
    "room_rate","int(4)","NO"
    "room_bed","varchar(6)","NO"
    bookingtable
    "Field","Type","Null","Key"
    "book_id","varchar(3)","NO","PRI"
    "room_no","varchar(3)","YES"
    "date_fro","datetime","YES"
    "date_to","datetime","YES"
    "no_of_adults","int(3)","YES"
    "no_of_child","int(3)","YES"

    I tried this query to got my result and I got the list of all rooms which are not booked -
    SELECT roomdetail.room_no,room_type,room_bed,room_rate FROM roomdetail LEFT JOIN bookingtable
    ON roomdetail.room_no=bookingtable.room_no
    WHERE bookingtable.room_no IS NULL;


    I tries this also but I got blank output-
    SELECT roomdetail.room_no,room_type,room_bed,room_rate FROM roomdetail LEFT JOIN bookingtable
    ON roomdetail.room_no=bookingtable.room_no
    WHERE DATE(date_fro)>='2012-01-08' AND DATE(date_to)<='2012-01-11' AND bookingtable.room_no IS NULL;

    Can any one please help me to solve this problem.

    Thank you to all for cooperation.
    Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was going to find the same link that Rudy posted

    I draw out Rudy's diagram near the foot of that page almost every time I have to solve a problem like this
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •