Product Price Range Query

I've noticed that some search queries on e-commerce sites return "item
price range hyperlinks" as filters for matched items.
So you would have something like:
Under $50 $75-$100 $150-$250
in a row with only the applicable price ranges to the queried products
showing. So, with the above example, if there weren't any products
between $101 and $149, that price range wouldn't show.
I'm wondering about the structure of the SQL for this type of query.
On the surface, I could do multiple queries for each of the price
ranges I needed and union them together, but I'm wondering if there is
a more efficient way to do it in one pass.
Ian<dontspammenow@.yahoo.com> wrote in message
news:1132866827.910562.120930@.g43g2000cwa.googlegroups.com...
> I've noticed that some search queries on e-commerce sites return "item
> price range hyperlinks" as filters for matched items.
> So you would have something like:
> Under $50 $75-$100 $150-$250
> in a row with only the applicable price ranges to the queried products
> showing. So, with the above example, if there weren't any products
> between $101 and $149, that price range wouldn't show.
> I'm wondering about the structure of the SQL for this type of query.
> On the surface, I could do multiple queries for each of the price
> ranges I needed and union them together, but I'm wondering if there is
> a more efficient way to do it in one pass.
> Ian
>
Put your possible price ranges in a table:
CREATE TABLE price_ranges (low_price DECIMAL(10,2) NOT NULL, high_price
DECIMAL (10,2) NOT NULL, CHECK (low_price < high_price), PRIMARY KEY
(low_price, high_price));
INSERT INTO price_ranges (low_price, high_price)
SELECT 1, 50 UNION ALL
SELECT 50, 75 UNION ALL
SELECT 75, 100 UNION ALL
SELECT 100, 150 UNION ALL
SELECT 150, 250 UNION ALL
SELECT 250, 99999999 ;
Now try one of these queries:
SELECT R.low_price, R.high_price
FROM northwind.dbo.products AS P,
price_ranges AS R
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */
GROUP BY R.low_price, R.high_price ;
SELECT R.low_price, R.high_price
FROM price_ranges AS R
WHERE EXISTS
(SELECT *
FROM northwind.dbo.products AS P
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */) ;
David Portas
SQL Server MVP
--