I have two tables:
Reservations Table (I separated them by date just for organizational purposes)
Date Name Room Name Seats Requested
01/1/2020 John 1 10
01/1/2020 Rebecca 3 30
01/1/2020 Haley 6 55
01/2/2020 David 1 10
01/2/2020 Rebecca 3 30
01/2/2020 Dasher 4 30
01/3/2020 Roger 4 40
01/3/2020 Doe 5 50
01/3/2020 Smith 3 30
Room Table
Name Seats
1 10
2 20
3 30
4 40
5 50
6 60
Each room will only appear ONCE for each date or won't appear at all. They are also on a "best fit" basis, as can see in the middle 3, both Rebecca and Dasher needed 30 seats, but since Rebecca got room 3, which is the best fitted room for her (30 seats) Dasher has to get the next "best" fit room for his 30 requested seats which is room 4 that has 40 seats available.
Now I have to "remove" a room from both tables: so the tables after like that would be something like this:
Reservations Table (AFTER REMOVING ROOM 3)
Date Name Room Name Seats Requested
01/1/2020 John 1 10
01/1/2020 Rebecca 30
01/1/2020 Haley 6 55
01/2/2020 David 1 10
01/2/2020 Rebecca 30
01/2/2020 Dasher 4 30
01/3/2020 Roger 4 40
01/3/2020 Doe 5 50
01/3/2020 Smith 30
Room Table (AFTER REMOVING ROOM 3)
Name Seats
1 10
2 20
4 40
5 50
6 60
So I want to write an SQL Query that will find the appropriate room that will be available to the people that got their room removed in the reservations table.
For example For date 1 (01/1/2020) Rebecca loses her room 3. Which means her next best room she can use is room 4.
For date 2 we see Rebecca again loses her room 3, except this time, room 4 is being used, which means her next best room she can use is room 5.
For date 3, Smith loses his room 3. But since room 4 and 5 is being used, his next available room he can use is room 6
My ideal output would be something like this:
Date Name Room Name Seats Requested
01/1/2020 Rebecca 4 30
01/2/2020 Rebecca 5 30
01/3/2020 Smith 6 30
I've already been able to remove the rooms as shown above, so that won't be necessary. Below is the SQL query I'm trying, but I'm getting syntax errors.
SELECT date,name,roomName,seatsRequested
FROM reservations table1
where not exists (select 1
from Room table2
where table1.roomName = table2.Name)
AND MIN(table1.seatsRequested<=table2.seats)
GROUP BY date;
First I select the stuff I want to display. Then I want to find the rooms that don't exist in for a specific date.
Then I want to find the first room that will fit, so I use the "MIN()" function to do that, because for example, for date 1, if I didn't use the MIN() function, it would return two values: Room 4 and Room 5 because both of them are available for Rebecca to use. Using "MIN()" would hopefully only return Room 4.
Finally I group the query by the date so it applies to each date.
Just to clarify, I don't need any data inserted yet, just returned. If it's easier to simply just insert the data, then that is fine, but getting the returned stuff is really what I'm looking for.
I'm getting syntax errors (A plethora, things like GROUP BY isn't allowed, or like can't use MIN like that). And when I fix those errors I get nothing back. Not sure how to do this. Is this possible in SQL?
Each person will reserve a room separately. For example, Jim wants to reserve a room with 25+ seats for 01/1/2020. Rooms 1,3,6 are already reserved, room 2 does not match, so rooms 4 and 5 are matching and available, and room 4 must be shown/reserved for him. You do not need to investigate the reservations for another dates. Jim simply enters "01/1/2020" and "25 places" and receives "room 4". That's all. Is it? – Akina
@Akina yes, that's right. I was using java for my inputs and removal. now I just need SQL to return me the values for each one and then I'll use java to insert them. but if SQL has the functionality perhaps I don't need to use JAVA at all. – PSU Change
SELECT Room.Name
FROM Room
LEFT JOIN Reservation ON Room.Name = Reservation.RoomName
AND Reservation.Date = {entered date}
WHERE Reservation.RoomName IS NULL
AND Room.Seats >= {entered amount}
ORDER BY Room.Seats ASC LIMIT 1
If (in current data state) Jim enters 01/1/2020 as date and 25 as amount then the query will return 4. If Jim agreed then this room is reserved and according row is inserted into Reservations. If he not agreed he may alter the amount and/or date and maybe receive another room or refuse to reserve a room at all.
Also you may remove LIMIT 1 - in such case Jim will see 2 variants for reservation, rooms 4 and 5. He'll select one of them by some reasons (for example, reservation price may influence), and according info will be added into the table.