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
August 26, 2008 at 11:47 am
The better approach would be to employ a normalized database design.
Based on your query, I can see that Product exists in table Products multiple times.
Split your table on three tables: Product; Category; ProductCategory and enjoy a performance benefit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply