sql distinct join - matching two tables in sequences [closed]

First, I've never heard of a hotel that didn't take dates into account when assigning rooms, but that is another matter.

More importantly, this type of matching is not appropriate for SQL. SQL can generate all possibilities of requests matching rooms. However, coming up with a definitive single mapping is not appropriate.

Why? First, there are many such solutions. In your little example, Muller and King can be assigned rooms 5 and 6 -- but in either order. For two matches, there are just two possibilities. Consider if you had 10 identical rooms and 10 identical requests. How many different ways can these be assigned? Which single one do you want to choose?

This becomes even more problematic if the matches are fuzzy, because they don't exactly match the requests. That is, you are left over with King, but your last room has two beds but is large. In a real world problem, you would have to make multiple judgements on which is best.

This is an optimization problem. It might actually be a linear programming problem. If so, it has a deterministic and optimal algorithm that does not require searching through all possible assignments.