Best way to use DISTINCT values in a SQL view as a dynamic source for another table in the DB
Posted on 2008-06-17
I'm not sure the best way to describe what I need but here goes. I'm currently building a Tag Cloud functionality for a site in ASP.NET 2 with c# which displays the most popular searches on a site. I'm logging each searchterm in a table and the language it was searched in and using a stored procedure to aggregate the top 10 search terms with the itemcount as source for the tag cloud. As below:
SELECT DISTINCT TOP 10 SearchTerm,
FROM SearchesTracking WHERE SearchTerm = s.SearchTerm AND Language = @lang) AS ItemCount
FROM SearchesTracking s
WHERE Language = @lang
ORDER BY SearchTerm
I've been asked to create an admin screen wher the user can see a view of all the aggregated search terms and then select which ones are published into the tag cloud via a check/uncheck box. Herein lies my problem, as the tracking table I have at the moment records every search term and then I aggregate it
in a SQL query. Ideally I would create a reference table say TermStatus with 2 rows, Published and NotPublished. A middle table with a the ID of the distinct searchterm and the TermStatus field.
But since the aggregated search terms are dynamic , this is not possible. Reading up on Views they seem difficult to implement an updateable view on this and plus I don't think it would generate a ID value!
What would be the best way to achieve this do you think? Apologies if this is unclear. Please ask for any clarification. Thanks very much in advance