MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

PHP-DB - Selecting the dates with out duplicates


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 10th, 2013, 08:26 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation Power: 0
PHP-DB - 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.
Reply With Quote
  #2  
Old January 10th, 2013, 08:30 AM
ManiacDan's Avatar
ManiacDan ManiacDan is online now
Sarcky
Dev Shed God 10th Plane (9500 - 9999 posts)
  
Join Date: Oct 2006
Location: Pennsylvania, USA
Posts: 9,919 ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)  Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 34 m 50 sec
Reputation Power: 6113
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.
Reply With Quote
  #3  
Old January 10th, 2013, 08:56 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation Power: 0
Quote:
Originally Posted by ManiacDan
What database are you using?


Am using MySql database
Reply With Quote
  #4  
Old January 10th, 2013, 09:35 AM
ManiacDan's Avatar
ManiacDan ManiacDan is online now
Sarcky
Dev Shed God 10th Plane (9500 - 9999 posts)
  
Join Date: Oct 2006
Location: Pennsylvania, USA
Posts: 9,919 ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)  Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 34 m 50 sec
Reputation Power: 6113
Thread moved to the MySQL forum.
Reply With Quote
  #5  
Old January 10th, 2013, 10:15 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141
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"
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6  
Old January 10th, 2013, 10:20 AM
ManiacDan's Avatar
ManiacDan ManiacDan is online now
Sarcky
Dev Shed God 10th Plane (9500 - 9999 posts)
  
Join Date: Oct 2006
Location: Pennsylvania, USA
Posts: 9,919 ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)  Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 34 m 50 sec
Reputation Power: 6113
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.
Reply With Quote
  #7  
Old January 10th, 2013, 10:24 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141
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
Reply With Quote
  #8  
Old January 10th, 2013, 10:27 AM
ManiacDan's Avatar
ManiacDan ManiacDan is online now
Sarcky
Dev Shed God 10th Plane (9500 - 9999 posts)
  
Join Date: Oct 2006
Location: Pennsylvania, USA
Posts: 9,919 ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)ManiacDan User rank is General 77th Grade (Above 100000 Reputation Level)  Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 34 m 50 sec
Reputation Power: 6113
But I like trees!

Thanks. I still need to read your book. I bought it. It's on my dresser.
Reply With Quote
  #9  
Old January 10th, 2013, 11:03 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141
Quote:
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
Reply With Quote
  #10  
Old January 11th, 2013, 05:37 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation Power: 0
Quote:
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.
Reply With Quote
  #11  
Old January 11th, 2013, 06:16 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141
Quote:
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
Reply With Quote
  #12  
Old January 15th, 2013, 08:22 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation 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>
Reply With Quote
  #13  
Old January 15th, 2013, 08:27 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation 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>
Reply With Quote
  #14  
Old January 15th, 2013, 08:32 AM
kcachilove kcachilove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2012
Posts: 11 kcachilove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 46 m 51 sec
Reputation 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.
Reply With Quote
  #15  
Old January 18th, 2013, 05:28 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141
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
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Databases > MySQL Help > PHP-DB - Selecting the dates with out duplicates

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap