Sql Query A Range Of Records That Fall Within A Min And Max Value

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 f

Solution 1:

and Price between@minpriceand@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)

Solution 2:

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
ORDERBY ID desc

Solution 3:

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)
  )
ORDERBY 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.

Solution 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.

CREATEPROCEDURE [dbo].[spGetProperties] 
      @styleVARCHAR(50)  =NULL
     ,@regionVARCHAR(50)  =NULL
     ,@bedrooms  TINYINT      =NULL
     ,@bathrooms TINYINT      =NULL
     ,@minPriceINT=NULL
     ,@maxPriceINT=NULLASBEGINSELECT busname, email, render_pic, 
        area,logo, url, email, map, 
        description, tag, catch_phrase, region
     FROM   Results
         WHERE    (@styleISNULLOR STYLE LIKE'%'+@style+'%')
              AND (@regionISNULLOR REGION LIKE'%'+@region+'%')
              AND (@bedroomsISNULLOR BEDROOMS >=@bedrooms)
              AND (@bathroomsISNULLOR BATHROOMS >=@bathrooms)
              AND (@minPriceISNULLOR MINPRICE >=@minPrice)
              AND (@maxPriceISNULLOR MAXPRICE <=@maxPrice)
     ORDERBY ID descEND