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.