August 14, 2008 at 1:37 pm
Hi All,
I need to design queries for an e-commerce site's search page. The basic premise is that I search on products, and the search results page will display a matching product list, but also display a list of categories that the products fall into.
For example: if you goto http://www.ebay.com and search on "armour (845)" (without the quotation marks) you see all matching items, but also a list of categories that the items fall into, as well as a count of items in those categories.
Does anyone have a "best practices" way to go about returning the products AND the categories with counts? My initial thought was to create a stored procedure that would select all matching products into a temp table, then query the temp table to get the categoriy breakdown. Something like below:
--Get all Matching Products
Select ProductName,CategoryName
Into #TempProducts
From Product
Where ...
--Return Category Breakdown
Select CategoryName,count(CategoryName)
From #TempProducts
Group By CategoryName
--Return Matching Products
Select * From #TempProducts
Does anyone have a better approach?
Thanks for Looking!
August 15, 2008 at 2:03 pm
can you do this instead. It would require less i/o then a temp table
--Get all Matching Products
Select ProductName,CategoryName, TheCount
From Product
INNER JOIN
(SELECT CategoryName,count(CategoryName) TheCount
From Products
Where ...
) sub
ON CategoryName = sub.CategoryName
WHERE ....
The counts would be like
Armour shield Armour 10
Armour belt Armour 10
Armour helmet Armour 10
In code they could make that work
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply