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 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!
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)
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
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.
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
精彩评论