How do I join this sql query to another table?

Question

I have the following SQL query and so far it works the way it should and gets the top 40 tag ids that I have stored in the tagmap table.

SELECT TOP 40
tbrm_TagMap.TagID,
Count(*)
FROM tbrm_TagMap
GROUP BY tbrm_TagMap.TagID
ORDER BY COUNT(tbrm_TagMap.TagID) DESC

I also want to join to the Tags table which contains the actual name of each TagID. Each attempt I make comes back with an error. How can I achieve this? I am using SQL 2008.

Answer 1

SELECT  *
FROM    (
        SELECT  TOP 40 
                tbrm_TagMap.TagID, COUNT(*) AS cnt
        FROM    tbrm_TagMap
        GROUP BY
                tbrm_TagMap.TagID
        ORDER BY
                COUNT(*) DESC
        ) q
JOIN    Tags
ON      Tags.id = q.TagID
ORDER BY
        cnt DESC

Answer 2

My guess is that when you were joining tags, you weren't including it in the group by clause, which will always through an error in SQL Server. Every column not aggregated but returned needs to be in the group by.

Try something like this:

SELECT TOP 40
    tbrm_TagMap.TagID,
    t.Tag,
    Count(*)
FROM 
    tbrm_TagMap
    INNER JOIN tags t ON
        tbrm_TagMap.TagID = t.TagID
GROUP BY 
    tbrm_TagMap.TagID, 
    t.Tag
ORDER BY 3 DESC

Answer 3

SELECT TOP 40
tbrm_TagMap.TagID, Tags.TagName Count(*)
FROM tbrm_TagMap INNER JOIN Tags ON tbrm_TagMap.TagID = Tags.TagID
GROUP BY tbrm_TagMap.TagID, Tags.TagName
ORDER BY COUNT(tbrm_TagMap.TagID) DESC

Answer 4

Try this..

SELECT top 40 tags.TagDescription, tbrm_TagMap.TagID, Count(*)
FROM tbrm_TagMap
    INNER JOIN Tags 
       ON TagMap.TagID = Tags.TagId
GROUP BY tags.TagDescription, tbrm_TagMap.TagID
ORDER BY COUNT(tbrm_TagMap.TagID) DESC