I have two tables:
dbo.allowedURLs (a unique list of all permitted domains)
*** DomainName *** google.com facebook.com etc...
dbo.loggedURLs (a list of URLs processed by a proxy server)
*** Username *** *** URL **** fred.smith www.google.com/something-else-here joe.black www.othersite.net/search/query
I need to be able to return a list of all the domain names in the 'allowedURLs' table and a count of how many different users accessed the service. The 'url' field in the 'loggedURLs' table contains the domain name within the full URL string.
*** DomainName *** *** Count of Username **** google.com 1
While the query below gives me the results I'm after, I've been having significant performance issues running this and I was just wondering if anyone has advice on a better way to achieve this same outcome?
SELECT dbo.allowedURLs.DomainName, COUNT(DISTINCT dbo.loggedURLs.username) FROM dbo.loggedURLs INNER JOIN dbo.allowedURLs ON dbo.loggedURLs.URL LIKE '%' + dbo.allowedURLs.DomainName + '%' GROUP BY dbo.allowedURLs.DomainName
Thanks for any advice!