Counting records of a table with multiple foreign keys (MySQL)

Question

Hello,

I have a MySQL database with various tables whose records can be tagged, so I have a tags table, and a tag_associations table that relates tags to other "taggable" tables via multiple foreign keys, and also to an "owning" user -- my fields are something like the following:

• tag_association_id (primary key)
• user_id (tag association creator)
• tag_id (related tag)
• artist_id (tagged artist, can be null)
• album_id (tagged album, can be null)
• track_id (tagged track, can be null)

I basically want to count all items that have been tagged a particular tag -- so something along the results of this query:

SELECT
    COUNT(ta.tag_association_id) AS how_many
FROM
    tag_associations AS ta
    LEFT JOIN users AS u ON ta.user_id = u.user_id
WHERE
    ta.tag_id = '480'
    AND u.user_status = 'active'

But the problem with this query lies in cases where the same tag has been applied to the same item by multiple users, so if 2 different users tag the artist 'Bobby Jones Band' as 'rad', it counts both of the tag associations where I just want to count it once. I tried adding this to the above:

GROUP BY ta.artist_id, ta.album_id, ta.track_id

...which got me close, but didn't yield the exact results I needed -- it gave me multiple row results of different counts. Is there any magic I can use in a case like this and keep it in a single query? While remaining as efficient as possible, of course :) Thanks!

Answer 1

If I got your question right, then your GROUP BY should almost do the job.

This solution should get the unique rows from tag_associations and count them.

SELECT COUNT(*)
FROM
(
    SELECT 1
    FROM tag_associations AS ta 
    LEFT JOIN users AS u ON ta.user_id = u.user_id
    WHERE ta.tag_id = '480' 
    AND u.user_status = 'active' 
    GROUP BY ta.artist_id, ta.album_id, ta.track_id
) x