my sql query with possible combinations

Question

i have the search requirement that with doing search option for rental house.

city,price range,numberOfRooms are the criteria.

if the user does not mention any of the details and click submit we have to show all the results.

if the user mentioned for example price range is 5000 to 10000 then it has to give the results for considering all cities,all possible 1bhk,2bhk,3bhk...

how to write single query that covers all the possibilities

i have this idea in my mind..

let us consider only city and price are the option and no other options are there for the better explanation.

i create a table called city

city

option    cityname
  1        delhi
  2        hyderabad
  3        bangalore
  4        delhi
  4        hyderabad
  4        bangalore

SELECT *
FROM rental
WHERE city IN (select cityname from city where option=#{option_value}) and price>=#{minPrice} and price<=#{maxPrice};

Do we need to create the table city?

Is there any to send the values in list and based on option those list values should come.

is it correct?

Answer 1

Here is another way to do this, by using optional parameters something like:

SELECT * 
FROM rental 
WHERE 1 = 1
AND (@city IS NULL OR city = @city)
AND (@PricerangeFlag IS NULL OR price BETWEEN @minPrice AND @maxPrice)
..

If any of the parameters @city, @pricerangeFlag or any other parameter passed this way to this query, passed with a NULL value then the whole predicate, for example, AND (@city IS NULL OR city = @city) will be evaluated to true without getting into the second predicate OR city = @city since @city is NULL evaluates to true. The same with other parameters and if all the parameters passed with the value NULL to the query the WHERE clause wil be evaluated to WHERE 1 = 1 which is always true predicate, there for act like it wasn't presented and returns all rows.

Note that in case of the price ranges parameters I used a flag parameter to disable the two parameters @minPrice and @maxPrice