what is the query to get "related tags" like in stack overflow

i have 3 tables:

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

i am trying to support showing related tags like in SOF. so if you click on tags "XYZ", right now i am showing all the links with tag "XYZ" but i also want to show the distinct list of all other tags that pe开发者_运维百科ople have tagged those items that also have tagged" "XYZ"

what is the fastest way to query this

Try:

  SELECT t.tagname
    FROM TAGS t
    JOIN TAGS_BRIDGE tb ON tb.tagid = t.id
    JOIN (SELECT li.id
            FROM LINKS li
            JOIN TAGS_BRIDGE tb ON tb.linkid = li.id
            JOIN TAGS t ON t.id = tb.tagid
           WHERE t.tagname = 'XYZ') x ON x.id = tb.linkid
GROUP BY t.tagname

A very ugly nested query.

SELECT DISTINCT tagName FROM tags WHERE id in
(
    SELECT tagID FROM tagsBridge WHERE linkID IN
    (
        SELECT linkID FROM tagsBridge WHERE tagID IN
        ( 
            SELECT id FROM tags WHERE tagName like 'XYZ'
        )
    )
)

Edited: now this is basically is just a different way of writing Kirk Broadhurst's, I think. I guess some DB might handle it differently behind the scenes, but I think almost all modern engines would end up with the two of them having the same query plan.

select distinct t.tagName
from tags t
    join tagsBridge tb on (t.id = tb.tagID)
    join tagsBridge tbt on (tb.linkID = tbt.linkID)
    join tags ta on (ta.id = tbt.tagID)
where ta.tagname = 'XYZ'