SQL query a range of records that fall within a Min and Max value

Question

For my Realty site I would also like to return the properties that fall within the user's requested price range from two drop down lists MinPrice and MaxPrice (which are also the field values).

Below is my statement thus far. It is working properly aside from the above requirement.

SELECT busname, email, render_pic, 
       area,logo, url, email, map, 
       description, tag, catch_phrase, region
FROM   Results
WHERE  STYLE LIKE 'varStyle' 
   AND REGION LIKE 'varRegion' 
   AND BEDROOMS LIKE 'varBedrooms' 
   AND BATHROOMS LIKE 'varBathrooms'
ORDER BY ID desc

Thank you SO MUCH in advance!

Answer 1

and Price between @minprice and @maxprice

Edit: To cover what @paxdiablo is suggesting, although I have no idea why a house would have anything besides a price...

and (minprice between @minprice and @maxprice 
or maxprice between @minprice and @maxprice
or @minprice between minprice and maxprice)

Answer 2

Having two ranges that overlap (in the input and record) is a little ticky but you should try:

SELECT
    busname, ...
FROM Results
WHERE STYLE LIKE &varStyle
  AND REGION LIKE &varRegion
  AND BEDROOMS LIKE &varBedrooms
  AND BATHROOMS LIKE &varBathrooms
  AND (
         MINPRICE between &MinPrice and &MaxPrice
      OR MAXPRICE between &MinPrice and &MaxPrice
      OR (MINPRICE <= &MinPrice AND MAXPRICE >= &MaxPrice)
  )
ORDER BY ID desc

This will match if any price within your row min/max range is within your search min/max range (including when row min/max is entirely within search range) or if the entire search range is within your row range, which is what I think you were after.

In addition, you may want to rethink using LIKE unless those var... variable are actually allowed to have wild-cards. Some less intelligent DBMS' may not convert them into a more efficient = operation automatically.

Answer 3

SELECT busname, email, render_pic, 
       area,logo, url, email, map, 
       description, tag, catch_phrase, region
FROM   Results
WHERE  STYLE LIKE 'varStyle' 
   AND REGION LIKE 'varRegion' 
   AND BEDROOMS LIKE 'varBedrooms' 
   AND BATHROOMS LIKE 'varBathrooms'
   AND Price between varMinPrice and varMaxPrice
ORDER BY ID desc

Answer 4

This is how I would perform this type of query:

Only pass in values that are chosen by the user anything else that they don't specify leave as they are defaulted to NULL in the sproc below. I guessed at the parameter types but you get the idea. Also have added '%' so that the Region and Style are wild-carded.

The below query will return matching results based on none, any or all combinations of parameters supplied.

CREATE PROCEDURE [dbo].[spGetProperties] 
      @style     VARCHAR(50)  = NULL
     ,@region    VARCHAR(50)  = NULL
     ,@bedrooms  TINYINT      = NULL
     ,@bathrooms TINYINT      = NULL
     ,@minPrice  INT          = NULL
     ,@maxPrice  INT          = NULL    
AS
BEGIN

     SELECT busname, email, render_pic, 
        area,logo, url, email, map, 
        description, tag, catch_phrase, region
     FROM   Results
         WHERE    (@style IS NULL OR STYLE LIKE '%' + @style + '%')
              AND (@region IS NULL OR REGION LIKE '%' + @region + '%')
              AND (@bedrooms IS NULL OR BEDROOMS >= @bedrooms)
              AND (@bathrooms IS NULL OR BATHROOMS >= @bathrooms)
              AND (@minPrice IS NULL OR MINPRICE >= @minPrice)
              AND (@maxPrice IS NULL OR MAXPRICE <= @maxPrice)
     ORDER BY ID desc

END 

Answer 5

Hello, and THANK YOU everyone for your replies!

However have not been successful as of yet.

Strange, as if I enter values in my between statement AND PRICE BETWEEN '200,000' AND '500,000' it works!

but when trying to pull from the field values AND PRICE BETWEEN 'MinPrice' AND 'MaxPrice' no results?

again, Thanking you so much in advance, this is the last thing needed before a LOOONNG awaited launch of the site