0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gardeners and landscapers

WITH TopTags AS ( 
    SELECT top 100
        tagid
    FROM 
        posttags
    GROUP BY 
        tagid
    ORDER BY 
        COUNT(*) DESC
)

SELECT 
    t1.Tagname AS Tag1Name,
    t2.Tagname AS Tag2Name,
    COUNT(*) AS CombinationCount
FROM 
    posttags pt1
JOIN 
    posttags pt2 ON pt1.postid = pt2.postid AND pt1.tagid < pt2.tagid
JOIN 
    tags t1 ON pt1.tagid = t1.id 
JOIN 
    tags t2 ON pt2.tagid = t2.id 
WHERE 
    pt1.tagid IN (SELECT tagid FROM TopTags) AND 
    pt2.tagid IN (SELECT tagid FROM TopTags) 
GROUP BY 
    t1.Tagname, t2.Tagname
ORDER BY 
    CombinationCount DESC;

view current version

edited dec 4

created dec 4 Janis123