Mvc List only available rooms and not busy ones

Asked

Viewed 65 times

1

I have the following problem: A reservation has associated with you a client and a room. When creating a reservation, I have to fill in some fields (such as customer name, date of arrival, date of departure and the room where you will be staying).

The selection of the room is made through a dropdownlist that shows all the rooms created. The problem is this same, I do not want to show all the rooms created, but yes, all the rooms available, ie those who have no reservations at the date of creation of the reservation.

To query that I’ve assembled so far is this:

SELECT Quarto.ID_Quarto FROM Quarto
LEFT OUTER JOIN Reserva ON Quarto.ID_Quarto = Reserva.ID_Quarto
WHERE DataEntrada > GETDATE()
And DataSaida < GETDATE()
OR Reserva.ID_Quarto IS NULL
sql asp.net-mvc

  • I didn’t understand that: Aqueles que não tem reservas na data de criação da reserva.. I could explain your question better?

  • When I make a reservation... I have a room attribute ... in this attribute I have a dropdownlist that shows me all the rooms I have... whether they are occupied or not. What I intend is that if someone will now book room 1 and then someone else will book another room... in this dropdown list room 1 as it has already been occupied, it cannot appear on this dropdown list. I just want that room to appear again... when the date of departure is given ( check out)

  • Is your Date field only filled in after the person leaves? I believe that what you need to use is not exists!

  • because my idea was... if you give the date of entry the room is reserved, if you give the date of exit, it is already free. Do you think that’s correct or do you have any better suggestions?

1 answer

1


Assuming that the date of departure will always be greater that of the input, you can use the following:

SELECT DISTINCT Quarto.ID_Quarto
FROM Quarto Q1
LEFT JOIN Reserva R1 ON Q1.ID_Quarto = R1.ID_Quarto
WHERE Reserva.ID_Quarto IS NULL
   OR NOT EXISTS (SELECT 1 FROM Quarto Q2 
                  LEFT JOIN Reserva R2 ON Q2.ID_Quarto = R2.ID_Quarto
                  WHERE Q1.ID_Quarto = Q2.ID_Quarto
                    AND R2.DataEntrada <= GETDATE() 
                    AND (R2.DataSaida IS NULL OR R2.DataSaida > GETDATE())
                    -- o is null é desnecessário se a data de saída é previamente cadastrada

  • The problem is that so in the results I get all the rooms... I just want to get those rooms that have no reservations. I would like to implement this... the customer when booking the room... until he checks out... that room may not be available for other reservations

  • @Heftysilva gave a revised answer, see if so resolves.

  • Thanks for trying to help me. This is giving me an error because the Q2 ( re-fereste table room) has no date of entry or exit... these attributes only have them in the reservation table

  • SELECT Quarto.Id_quarto FROM Quarto LEFT JOIN Reserva ON Quarto.Id_quarto = Reserva.Id_quarto WHERE Reserva.Id_quarto IS NULL AND NOT EXISTS (SELECT 1 FROM Reserva Q2 WHERE Quarto.Id_quarto = Q2.Id_quarto AND Q2.Date input <= GETDATE() AND (Q2.Date output IS NULL OR Q2.Date output > GETDATE()))

  • got it.. I changed the internal consultation to do the same Oin of the external, see if it solves.

  • I did so and it appears to me the room that has no reservation... but since the others already had reservations but was given the check out... should appear also

  • Changing the AND for OR must resolve (on line AND NOT EXISTS). I changed it again, see if it’s :p

  • Yes, with this solution shows me the ID of the rooms , but repeats ... example in room 1 I had 3 reservations and in room 2 no . he shows me 1, 1, 1, 2

  • DISTINCT remedy this problem ;) modified response

  • Perfect, that’s what it is! if you wish to do so as follows... if you enter a date of entry and exit between today and tomorrow , respectively, if you already have a reservation for that room on those days , would for example appear a message saying that the room is already busy for those days ... it is possible?

  • For this case, you have to think code level, not bank consultation. The indicated is you mark this answer as accepted (if we solve the problem) and create a new question with that other question, you know!? ;)

Show 6 more comments

Browser other questions tagged sql asp.net-mvc

You are not signed in. Login or sign up in order to post.