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
Show quoteHide quotePut 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 .... ??? */) ;

Dynamic Stored Procedure
Dynamic WHERE & Dates
selecting from a 6 month date range
Sum() And Nulls
updating column