Count using like in SQL - how to?

Question

I got a two tables

tblGoals
-name
-url
-siteID

tblUrlCollection
-url
-siteID

How do I count how many times every "url" from tblGoals is in tblUrlCollection using "url" and "siteID" as input?

Would like to use like or something so I can add wildcards to the end of the "url" input parameter.

Please help me out - thanks a lot.

Performance is not a big issue here - backend stuff.

Answer 1

I think this should work:

SELECT tblGoals.url, COUNT(tblGoals.url)
FROM tblUrlCollection
INNER JOIN tblGoals ON tblGoals.url = tblUrlCollection.url AND tblGoals.siteID = tblUrlCollection.siteID
GROUP BY tblGoals.url

Answer 2

DECLARE @SiteId int
DECLARE @url varchar(100)
SET @url = 'http://stackoverflow.com%'
SET @SiteId = 10

SELECT g.url, COUNT(u.url) AS C
FROM tblGoals g
LEFT OUTER JOIN tblUrlCollection u
ON g.siteID = u.siteID AND g.url = u.url
WHERE g.url LIKE @url and g.Site=@SiteId
GROUP BY g.url