Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0

    Selecting the dates with out duplicates


    Hello, please am having issues querying my reports from the database. i want to create a reports from a number tables
    (
    rooms ==> this table is where i have all the room numbers.
    bookings ==> this table is where i have all the room numbers used per each date.
    guests ==> this table is where i have all the guest.
    reservation ==> this table is where i have records on the reservaed rooms and dates.
    booking ==>this table is where i have records on the checked-in rooms and dates.

    ) using joining,

    i created a view with this query

    CREATE VIEW
    roomstatebydate AS select rooms.roomid AS roomid,bookings.the_date AS the_date,
    rooms.roomno AS rmno,rooms.hotel_id AS hotel_id,booking.book_id AS book_id,
    booking.entryCode AS booking_entrycode,booking.roomid AS b_roomid,
    reservation.reservation_id AS reservation_id,reservation.entryCode AS R_entrycode,
    bookings.id AS id,bookings.id_item AS id_item,bookings.id_state AS id_state,
    bookings.id_booking AS id_booking,bookings.guestid AS bookings_guestid,
    bookings.hotel_id AS bookings_hotelid,bookings.entryCode AS entryCode,
    bookings.status AS status,bookings.reserved AS reserved,guests.guestid AS guestid,
    concat_ws(' ',guests.firstname,guests.middlename,guests.lastname) AS guest
    from ((((rooms left join bookings on(((rooms.roomno = bookings.roomno)
    and (rooms.hotel_id = bookings.hotel_id) and (bookings.status <> 1))))
    left join guests on((bookings.guestid = guests.guestid)))
    left join booking on((bookings.entryCode = booking.entryCode)))
    left join reservation on((bookings.entryCode = reservation.entryCode)))
    where (rooms.hotel_id = 1) order by rooms.roomno,bookings.the_date;

    i then select from the rooms left Join the view (roomstatebydate)

    select roomstatebydate.rmno, rooms.roomno,rooms.hotel_id,roomstatebydate.the_date,roomstatebydate.hotel_id ,
    roomstatebydate.id_state,roomstatebydate.reservation_id,roomstatebydate.roomid,roomstatebydate.guest
    from rooms left Join roomstatebydate ON rooms.roomno = roomstatebydate.rmno
    and rooms.hotel_id = roomstatebydate.hotel_id where rooms.hotel_id=1
    and roomstatebydate.the_date is Null or roomstatebydate.the_date='".$date."'

    $date is the date selected
    i want to print a report that will show whether a room is checked-in,reserved ,blocked or available on daily bases using a selected date.

    On Printing the report , it gives duplicate rows (room number) because of the activities in the bookings table,
    On using GROUP BY to fielter the duplicate ,it dose work but the date selection will be wrong . So i need a way to fielter the duplicate with out using group by.

    Please if there is any need to upload the table and its content please let me know then i will send it, since i can't attach file here.
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    What database are you using?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0
    Originally Posted by ManiacDan
    What database are you using?
    Am using MySql database
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Thread moved to the MySQL forum.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    may i make a suggestion, please

    first, please learn some formatting style

    your sql looks like a monolith of text, a block of letters, with no rhyme nor reason -- well, at least it's not all on one single long friggin line

    second, please dispense(with(needless (parentheses)))
    Code:
    SELECT rooms.roomid 
         , bookings.the_date 
         , rooms.roomno AS rmno
         , rooms.hotel_id 
         , booking.book_id 
         , booking.entryCode AS booking_entrycode
         , booking.roomid AS b_roomid
         , reservation.reservation_id 
         , reservation.entryCode AS R_entrycode
         , bookings.id 
         , bookings.id_item 
         , bookings.id_state 
         , bookings.id_booking 
         , bookings.guestid AS bookings_guestid
         , bookings.hotel_id AS bookings_hotelid
         , bookings.entryCode 
         , bookings.status 
         , bookings.reserved 
         , guests.guestid 
         , CONCAT_WS(' ', guests.firstname
                        , guests.middlename
                        , guests.lastname) AS guest 
      FROM rooms 
    LEFT 
      JOIN bookings 
        ON bookings.roomno = rooms.roomno 
       AND bookings.hotel_id = rooms.hotel_id
       AND bookings.status <> 1
    LEFT 
      JOIN guests 
        ON guests.guestid = bookings.guestid 
    LEFT 
      JOIN booking 
        ON booking.entryCode = bookings.entryCode 
    LEFT 
      JOIN reservation 
        ON reservation.entryCode = bookings.entryCode 
     WHERE rooms.hotel_id = 1 
    ORDER 
        BY rooms.roomno
         , bookings.the_date;
    third, if you want help debugging something like duplicates in a query, you will have to explain the primary/foreign one-to-many relationships between all your tables

    i, for one, am not about to try on my own to winkle out the nuances between tables called "booking" and "bookings"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Rudy, is there any functional reason to remove the parens? I admit, I'm paren-happy (and you've mentioned it about my queries before) and I never bothered to ask you. Especially with ambiguous associativity between languages and weird rules about nesting and precedence, I always felt that it was best to err on the side of explicitness.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    1. forest and trees, dude

    same reason i eliminated a whole bunch of useless column aliases in my rewrite of OP's query


    2. parens in FROM clause will, i believe, force execution sequence, which may not be the optimal path
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    But I like trees!

    Thanks. I still need to read your book. I bought it. It's on my dresser.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by ManiacDan
    It's on my dresser.
    collector's item -- they no longer print it, they only sell the ebook now

    if we ever get to meet in real life, i'll sign it for ya

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    may i make a suggestion, please

    first, please learn some formatting style

    your sql looks like a monolith of text, a block of letters, with no rhyme nor reason -- well, at least it's not all on one single long friggin line

    second, please dispense(with(needless (parentheses)))
    Code:
    SELECT rooms.roomid 
         , bookings.the_date 
         , rooms.roomno AS rmno
         , rooms.hotel_id 
         , booking.book_id 
         , booking.entryCode AS booking_entrycode
         , booking.roomid AS b_roomid
         , reservation.reservation_id 
         , reservation.entryCode AS R_entrycode
         , bookings.id 
         , bookings.id_item 
         , bookings.id_state 
         , bookings.id_booking 
         , bookings.guestid AS bookings_guestid
         , bookings.hotel_id AS bookings_hotelid
         , bookings.entryCode 
         , bookings.status 
         , bookings.reserved 
         , guests.guestid 
         , CONCAT_WS(' ', guests.firstname
                        , guests.middlename
                        , guests.lastname) AS guest 
      FROM rooms 
    LEFT 
      JOIN bookings 
        ON bookings.roomno = rooms.roomno 
       AND bookings.hotel_id = rooms.hotel_id
       AND bookings.status <> 1
    LEFT 
      JOIN guests 
        ON guests.guestid = bookings.guestid 
    LEFT 
      JOIN booking 
        ON booking.entryCode = bookings.entryCode 
    LEFT 
      JOIN reservation 
        ON reservation.entryCode = bookings.entryCode 
     WHERE rooms.hotel_id = 1 
    ORDER 
        BY rooms.roomno
         , bookings.the_date;
    third, if you want help debugging something like duplicates in a query, you will have to explain the primary/foreign one-to-many relationships between all your tables

    i, for one, am not about to try on my own to winkle out the nuances between tables called "booking" and "bookings"
    Rudy, thanks so much for the formatting, i want to give you the primary keys and foreign keys

    rooms.roomno ==>primary key

    bookings.hotel_id ==> foreign key
    rooms.hotel_id ==> foreign key

    guests.guestid ==>primary key
    bookings.guestid ==> foreign key

    booking.entryCode ==>primary key
    bookings.entryCode ==>foreign key

    reservation.entryCode ==>primary key
    bookings.entryCode ==>foreign key


    this rooms table is the table that takes all the rooms created under the hotel

    (db table) rooms
    roomid ==>primary key
    ,roomno ==>primary key
    ,hotel_id ==>foreign key


    bookings.roomno ==> foreign key
    bookings table takes the activities of each room at every point and is also use for the dynamic calender used in the reservation form and booking form.

    (db table) bookings
    the_date (date reserved/check-in/blocked is daily)
    ,id_state (reserved/check-in/blocked ) ==> foreign key
    ,roomno (room no from rooms table)==> foreign key
    ,guestid (keep the ID of the guest from the guest table)==> foreign key
    ,hotel_id (hotel ID) ==> foreign key
    ,entryCode (use to get the record from booking/reservation table) ==> foreign key
    ,status (active or not)


    this table keeps the booking records
    (db table) booking
    book_id ==>primary key
    ,entryCode (the unique code use to track the record in bookings table) ==>primary key
    ,roomid (roomid from rooms table)==> foreign key
    ,guestid (keep the ID of the guest from the guest table)==> foreign key

    this table keeps the reservation records
    (db table) reservation
    reservation_id ==>primary key
    ,entryCode (the unique code use to track the record in bookings table) ==>primary key
    ,roomid (roomid from rooms table)==> foreign key
    ,guestid (keep the ID of the guest from the guest table)==> foreign key

    the guest table keeps the record of the all the guests
    (db table) guests
    guestid ==>primary key
    ,hotel_id ==> hotel ID
    ,lastname==> guest name
    ,firstname==> guest name
    ,middlename==> guest name

    what i want to achieve with this reports is to print out all the room number from rooms table check from the bookings table whether the room is booked/reserved/block, if it is not in the bookings table for that day that means is vacant for that day.
    it to be printed using daily date, so on printing it should display all room numbers and show the once booked/reserved/block/vacant.
    thanks
    Onyeka.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by kcachilove
    On Printing the report , it gives duplicate rows (room number) because of the activities in the bookings table,
    On using GROUP BY to fielter the duplicate ,it dose work but the date selection will be wrong . So i need a way to fielter the duplicate with out using group by.
    after reviewing your table descriptions, and this statement of the problem with the query, i'm afraid that a solution is still well beyond my grasp

    my suggestion is that you scale back the query, and involve only the rooms table and the bookings table, and then show some sample results from the query (without the GROUP BY) and then explain where the duplicate results are coming from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0
    Thanks Rudy,
    The query below is where am printing from rooms and bookings only, because no date is selected here i have duplicates on room numbers with different dates.

    Please all my query results are in HTML table, just to make it a little clearer since i can't attach file here.

    SELECT rooms.roomid
    , rooms.roomno
    , bookings.roomno as b_roomno
    , rooms.hotel_id
    , bookings.the_date
    , bookings.id
    , bookings.id_item
    , bookings.id_state
    , bookings.id_booking
    , bookings.guestid AS bookings_guestid
    , bookings.hotel_id AS bookings_hotelid
    , bookings.entryCode
    , bookings.status


    FROM rooms
    LEFT
    JOIN bookings
    ON bookings.roomno = rooms.roomno
    AND bookings.hotel_id = rooms.hotel_id
    AND bookings.status <> 1
    WHERE rooms.hotel_id = 1
    ORDER
    BY rooms.roomno;

    the result of this query is below


    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Query result</title>
    </head>
    <body>
    <table border=1>
    <tr>
    <td bgcolor=silver class='medium'>roomid</td>
    <td bgcolor=silver class='medium'>roomno</td>
    <td bgcolor=silver class='medium'>b_roomno</td>
    <td bgcolor=silver class='medium'>hotel_id</td>
    <td bgcolor=silver class='medium'>the_date</td>
    <td bgcolor=silver class='medium'>id</td>
    <td bgcolor=silver class='medium'>id_item</td>
    <td bgcolor=silver class='medium'>id_state</td>
    <td bgcolor=silver class='medium'>id_booking</td>
    <td bgcolor=silver class='medium'>bookings_guestid</td>
    <td bgcolor=silver class='medium'>bookings_hotelid</td>
    <td bgcolor=silver class='medium'>entryCode</td>
    <td bgcolor=silver class='medium'>status</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-14</td>
    <td class='normal' valign='top'>7</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120415</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-09</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120029</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>8</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120415</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>4</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120029</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>36</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>32</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130109123308</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>36</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-11</td>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>32</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130109123308</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>102</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>2</td>
    <td class='normal' valign='top'>103</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-11</td>
    <td class='normal' valign='top'>75</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110104739</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>74</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110104739</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-12</td>
    <td class='normal' valign='top'>76</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110104739</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>4</td>
    <td class='normal' valign='top'>105</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>5</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-14</td>
    <td class='normal' valign='top'>69</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>53</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110103417</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>5</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>70</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>53</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110103417</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-13</td>
    <td class='normal' valign='top'>94</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>91</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>96</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-12</td>
    <td class='normal' valign='top'>93</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-14</td>
    <td class='normal' valign='top'>95</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-11</td>
    <td class='normal' valign='top'>92</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>89</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-12</td>
    <td class='normal' valign='top'>86</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-14</td>
    <td class='normal' valign='top'>88</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-11</td>
    <td class='normal' valign='top'>85</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-16</td>
    <td class='normal' valign='top'>90</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-13</td>
    <td class='normal' valign='top'>87</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>84</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>7</td>
    <td class='normal' valign='top'>110</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>8</td>
    <td class='normal' valign='top'>111</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>112</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>113</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>114</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>12</td>
    <td class='normal' valign='top'>201</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>13</td>
    <td class='normal' valign='top'>202</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>14</td>
    <td class='normal' valign='top'>203</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>15</td>
    <td class='normal' valign='top'>204</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>16</td>
    <td class='normal' valign='top'>205</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>17</td>
    <td class='normal' valign='top'>206</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>18</td>
    <td class='normal' valign='top'>208</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>19</td>
    <td class='normal' valign='top'>209</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>20</td>
    <td class='normal' valign='top'>210</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>


    </table>
    </body></html>
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0
    Here am going to select date, when the date "2013-01-10"

    SELECT rooms.roomid

    , rooms.roomno
    , bookings.roomno as b_roomno

    , rooms.hotel_id
    , bookings.the_date
    , bookings.id
    , bookings.id_item
    , bookings.id_state
    , bookings.id_booking
    , bookings.guestid AS bookings_guestid
    , bookings.hotel_id AS bookings_hotelid
    , bookings.entryCode
    , bookings.status


    FROM rooms
    LEFT
    JOIN bookings
    ON bookings.roomno = rooms.roomno
    AND bookings.hotel_id = rooms.hotel_id
    AND bookings.status <> 1



    WHERE rooms.hotel_id = 1 and the_date is null or the_date='2013-01-10'
    ORDER
    BY rooms.roomno;

    this query will only print where the_date='2013-01-10' and where the _date is null, any other date that i have in bookings and is not 2013-01-10 will not be printed, that is the major problem am having.If you check this result below you notice that room 106 is missing cause it has a date that was not specify in bookings table


    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Data</title>
    </head>
    <body>
    <table border=1>
    <tr>
    <td bgcolor=silver class='medium'>roomid</td>
    <td bgcolor=silver class='medium'>roomno</td>
    <td bgcolor=silver class='medium'>b_roomno</td>
    <td bgcolor=silver class='medium'>hotel_id</td>
    <td bgcolor=silver class='medium'>the_date</td>
    <td bgcolor=silver class='medium'>id</td>
    <td bgcolor=silver class='medium'>id_item</td>
    <td bgcolor=silver class='medium'>id_state</td>
    <td bgcolor=silver class='medium'>id_booking</td>
    <td bgcolor=silver class='medium'>bookings_guestid</td>
    <td bgcolor=silver class='medium'>bookings_hotelid</td>
    <td bgcolor=silver class='medium'>entryCode</td>
    <td bgcolor=silver class='medium'>status</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>4</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120029</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>36</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>101</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>32</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130109123308</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>102</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>2</td>
    <td class='normal' valign='top'>103</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>104</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>74</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110104739</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>4</td>
    <td class='normal' valign='top'>105</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>91</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-10</td>
    <td class='normal' valign='top'>84</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>7</td>
    <td class='normal' valign='top'>110</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>8</td>
    <td class='normal' valign='top'>111</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>112</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>113</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>114</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>12</td>
    <td class='normal' valign='top'>201</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>13</td>
    <td class='normal' valign='top'>202</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>14</td>
    <td class='normal' valign='top'>203</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>15</td>
    <td class='normal' valign='top'>204</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>16</td>
    <td class='normal' valign='top'>205</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>17</td>
    <td class='normal' valign='top'>206</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>18</td>
    <td class='normal' valign='top'>208</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>19</td>
    <td class='normal' valign='top'>209</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>20</td>
    <td class='normal' valign='top'>210</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>


    </table>
    </body></html>
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    11
    Rep Power
    0
    I want to also use another date here (2013-01-15), the result did not print room 101 because it is in bookings table with a date that is not 2013-01-15.


    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Data</title>
    </head>
    <body>
    <table border=1>
    <tr>
    <td bgcolor=silver class='medium'>roomid</td>
    <td bgcolor=silver class='medium'>roomno</td>
    <td bgcolor=silver class='medium'>b_roomno</td>
    <td bgcolor=silver class='medium'>hotel_id</td>
    <td bgcolor=silver class='medium'>the_date</td>
    <td bgcolor=silver class='medium'>id</td>
    <td bgcolor=silver class='medium'>id_item</td>
    <td bgcolor=silver class='medium'>id_state</td>
    <td bgcolor=silver class='medium'>id_booking</td>
    <td bgcolor=silver class='medium'>bookings_guestid</td>
    <td bgcolor=silver class='medium'>bookings_hotelid</td>
    <td bgcolor=silver class='medium'>entryCode</td>
    <td bgcolor=silver class='medium'>status</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>50</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>100</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>8</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130109120415</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>102</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>2</td>
    <td class='normal' valign='top'>103</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>4</td>
    <td class='normal' valign='top'>105</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>5</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>106</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>70</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>53</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110103417</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>46</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>108</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>96</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>3</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>34</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>R20130110115152</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>6</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>109</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>2013-01-15</td>
    <td class='normal' valign='top'>89</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>0</td>
    <td class='normal' valign='top'>57</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>B20130110115118</td>
    <td class='normal' valign='top'>0</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>7</td>
    <td class='normal' valign='top'>110</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>8</td>
    <td class='normal' valign='top'>111</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>9</td>
    <td class='normal' valign='top'>112</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>10</td>
    <td class='normal' valign='top'>113</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>11</td>
    <td class='normal' valign='top'>114</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>12</td>
    <td class='normal' valign='top'>201</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>13</td>
    <td class='normal' valign='top'>202</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>14</td>
    <td class='normal' valign='top'>203</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>15</td>
    <td class='normal' valign='top'>204</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>16</td>
    <td class='normal' valign='top'>205</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>17</td>
    <td class='normal' valign='top'>206</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>18</td>
    <td class='normal' valign='top'>208</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>19</td>
    <td class='normal' valign='top'>209</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>

    <tr>
    <td class='normal' valign='top'>20</td>
    <td class='normal' valign='top'>210</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>1</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    <td class='normal' valign='top'>NULL</td>
    </tr>


    </table>
    </body></html>


    What i intend to achieve is to display all the room numbers from first to last not minding if is in bookings or not, then i use the date in bookings to identify if the room is booked or if it is vacant.
    I want when i select a date ,it will get to bookings table and get all the room numbers that falls into that date and mark them as booked then every other room numbers that are not in bookings for that date but are in rooms will also be printed and mark vacant.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    change this --
    Code:
      FROM rooms 
    LEFT 
      JOIN bookings 
        ON bookings.roomno = rooms.roomno 
       AND bookings.hotel_id = rooms.hotel_id
       AND bookings.status <> 1
     WHERE rooms.hotel_id = 1 and the_date is null or the_date='2013-01-10'
     ORDER 
        BY rooms.roomno
    to this --
    Code:
      FROM rooms 
    LEFT 
      JOIN bookings 
        ON bookings.roomno = rooms.roomno 
       AND bookings.hotel_id = rooms.hotel_id
       AND bookings.status <> 1
       and the_date='2013-01-10'
     WHERE rooms.hotel_id = 1 
     ORDER 
        BY rooms.roomno
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo