views:

192

answers:

3

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  A: 

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'
        )
    )
)
Kirk Broadhurst
Don't beat yourself, at least you made it **look** pretty by using proper indentation.
Esko
this doesn't seem to work as it returns 0 records always it seems
ooo
+1 Tested this and it works. It's also is more efficient than the accepted answer.
Andomar
+3  A: 

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
OMG Ponies
rexem. one more question, what if i have selected multiple tags. so i want to see related tags and i already filtered down on tagName ="XYZ" and tagname = "ABC" ??
ooo
A: 

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'
Zac Thompson
The `DISTINCT` in the `IN` clause isn't necessary - the tagnames are the only thing you need to be unique.
OMG Ponies