leora
leora

Reputation: 196589

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

what is the fastest way to query this

Upvotes: 1

Views: 883

Answers (3)

Zac Thompson
Zac Thompson

Reputation: 12685

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'

Upvotes: 0

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28728

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

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332631

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

Upvotes: 3

Related Questions