I am throwing this question out to the 'greater creativity' for some suggestions on search methodology that might solve this problem. I have come up with a somewhat contrived example to attempt to explain the problem well enough that some solution(s) might be recommended.
The problem: We are using a relational DB (MSSQL), and need to search across products with the following constraints:
We also need to group and sort on price, market, and larger entity roll-up.
Let me see if I can explain using semi-real world entities: Think of an application that allows users to search for hotel rooms that they can book. The following criteria might be available for the search: days that the rooms would be booked for, the market in which they want a hotel room (e.g. NYC, Boston), amenities that the hotel room(s) might have (hot tub, fireplace, number of beds, suite or not).
In all cases, the search criteria above may or may not be selected. So, a very broad search is possible (on these dates, I don't care which market), or varying the levels of specificity (in NYC, 2 beds, with fireplace).
To add complexity, we also need to return the prices of the hotel rooms for the selected criteria. Obviously, special pricing, different prices for the different days of the stay (one price on Thursday night and a different price on Friday and Saturday nights for a weekend stay).
Some of the things we've played around with are: Solr, Endeca, SQL querying, implementing in C# code and optimizing when/where necessary (think of the decorator pattern).
What I'm looking for here is some suggestions from the community on: which of the above is NOT suited for this problem, which might be well suited for this problem, which other technology might work better that we haven't thought about, techniques that others have used on similar problems that worked well (non-technology specific).