JamieC
Nov 2nd 2005, 7:24 am
Ok, here it is:
3 tables:
domains
======
DomainID, Domain
redirects
=======
RedirectID, DomainID, URL
searches
=======
SearchID, DomainID, Keywords
Every time a search is made, the details are stored in the 'searches' table, with a reference to the domainid that the visitor came from.
Redirects are specific entries that force a domain to go to a different page on our system.
The following query returns a list of NON REDIRECT domains, along with a ranked list of searched for keywords, and the number of times searched for, in our database.
SELECT temp_domains.domain, count(searches.searchid) AS searches, searches.keyword FROM (SELECT * FROM domains WHERE NOT EXISTS ( SELECT * FROM redirects WHERE redirects.domainid=domains.domainid)) AS temp_domains INNER JOIN searches ON temp_domains.domainid = searches.domainid GROUP BY temp_domains.domain, searches.keyword ORDER BY temp_domains.domain ASC, searches DESC;
What I would like to do is restrict the returned resultset in a number of ways.
For example, I would like to be able to set a number which 'searches' must be greater than for the row to be returned.
I would also like to be able to return only the top x rows per domain (so that I only see keywords that are relevant).
I'd be grateful for any suggestions,
Regards,
Jamie
3 tables:
domains
======
DomainID, Domain
redirects
=======
RedirectID, DomainID, URL
searches
=======
SearchID, DomainID, Keywords
Every time a search is made, the details are stored in the 'searches' table, with a reference to the domainid that the visitor came from.
Redirects are specific entries that force a domain to go to a different page on our system.
The following query returns a list of NON REDIRECT domains, along with a ranked list of searched for keywords, and the number of times searched for, in our database.
SELECT temp_domains.domain, count(searches.searchid) AS searches, searches.keyword FROM (SELECT * FROM domains WHERE NOT EXISTS ( SELECT * FROM redirects WHERE redirects.domainid=domains.domainid)) AS temp_domains INNER JOIN searches ON temp_domains.domainid = searches.domainid GROUP BY temp_domains.domain, searches.keyword ORDER BY temp_domains.domain ASC, searches DESC;
What I would like to do is restrict the returned resultset in a number of ways.
For example, I would like to be able to set a number which 'searches' must be greater than for the row to be returned.
I would also like to be able to return only the top x rows per domain (so that I only see keywords that are relevant).
I'd be grateful for any suggestions,
Regards,
Jamie