CODEWITHSUNDEEP
sqltags
leora
leora

Reputation: 196639

Generating tag cloud count given these 3 tables

I have these three table to store tags for photo albums. Here is the schema

table 1: Albums
Album_ID
Album_Name

table 2: AlbumTags
Tag_ID
Tag_Name

table 3: AlbumTagBridge
ID
Tag_ID
Album_ID

What is the most efficient SQL to be able to come up with a result set that looks like this:

Tag_Name | Count
Tag 1 | 19
Tag 2 | 3
Tag 3 | 17

Upvotes: 3

Views: 1162

Answers (2)

rick schott
rick schott

Reputation: 21127

SELECT dbo.AlbumTags.Tag_Name, 
       COUNT(dbo.AlbumTagBridge.Tag_Id) AS Cnt
FROM dbo.AlbumTagBridge 
INNER JOIN dbo.AlbumTags ON dbo.AlbumTagBridge.Tag_Id = dbo.AlbumTags.Tag_ID
GROUP BY dbo.AlbumTags.Tag_Name

Upvotes: 2

Alex Martelli
Alex Martelli

Reputation: 882023

Select Tag_Name, COUNT(AlbumTagBridge.ID)
From AlbumTags
Join AlbumTagBridge USING(Tag_ID)
Group By Tag_Name

Grouping by AlbumTags.Tag_ID, Tag_name might possibly be a tad cheaper depending on your indexing &c (especially if your SQL engine isn't all that smart;-), but since you tell us nothing about your indices nor about your engine this is about the best we can do.

Upvotes: 0

Related Questions