Getting count of related tags

Question

I recently asked this question to get related tags like in Stack Overflow: http://stackoverflow.com/questions/1648190/what-is-the-query-to-get-related-tags-like-in-stack-overflow

I would like to show the count like in Stack Overflow, so with each related tag, what is the count of this other tag on questions with the first tag?

Because these queries all have distinct in them. I don't think it is possible but obviously Stack Overflow must be doing this (unless they are doing this in multiple queries.)

Is there any way to get count of related tags all in the same query or must it be done in separate queries?

Answer 1

If that is the case then you could use a query like this...

links (id, linkName)
tags (id, tagName)
tagsBridge (tagID, linkID)

I think the query you'd want is this...

SELECT T.id, T.tagName, COUNT(TB.tagID) 
FROM tagsBridge TB 
INNER JOIN tags T ON TB.tagID = T.id GROUP BY T.id, T.tagName

Oh, my apologies, I did misunderstand the question... what I think you want then is this, albeit it does use nested queries...

SELECT T.id, T.tagName, COUNT(TB.tagID) 
FROM tagsBridge TB 
INNER JOIN tags T ON TB.tagID = T.id
WHERE TB.tagID IN (
  SELECT tagID FROM tagsBridge WHERE linkID IN (
    SELECT linkID FROM tagsBridge WHERE tagID IN (
      SELECT tagID FROM tagsBridge WHERE linkID = (THE SELECTED LINK)
    )
  )
)
GROUP BY T.id, T.tagName

This query should give you the list of tag ids, tag names and a count of each for all the selected link's tags and all tags associated with the tags of that link. Man that sounds confusing but I think it's what you're looking for.

Answer 2

You could search for the total count of related tags:

SELECT t2.tagname, count(distinct tb2.linkid) as RelatedLinkCount
FROM TAGS t2
JOIN TAGS_BRIDGE tb2 on t2.tagid = tb2.tagid
JOIN TAGS_BRIDGE tb1 on tb2.linkid = tb1.linkid
JOIN TAGS t ON t.id = tb1.tagid
WHERE t.tagname = 'tag3'
GROUP BY t2.tagname

Starting with the tag name (t), it looks for links for that tag (tb1), then it looks for tags that share a link (tb2). It looks up the name for the related tags (t2) and it's done :) No need to even join the LINKS table.