what is the query to get “related tags” like in st

问题:

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 people have tagged those items that also have tagged" "XYZ"

what is the fastest way to query this

回答1:

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

回答2:

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'
        )
    )
)

回答3:

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'