selecting reports using dates

[Kcachilove]

Hello, please am having issues querying my reports from the database. i want to creat 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.

[LastMitch]

@Kcachilove

Hello, please am having issues querying my reports from the database.

You post this in the wrong section. This question is actually more related to DATABASE than a PHP/MYSQL issue.

What database are you using?

You code is so cluster that it's really hard to read and understand. You should be have highlight the code (by clicking on the CODE button near the text editor) plus also try to organized it:

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."'