1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Adding complex conditionals to an SQL statement

Discussion in 'Databases' started by JamieC, Nov 2, 2005.

  1. #1
    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.
    SEMrush
    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
     
    JamieC, Nov 2, 2005 IP
    SEMrush