Join Query for Count in mysql statement

Question

SQL Fiddle

I have three db tables and I'd like to select the 10 most used tags from song_tag table, which I can do with the sql query below

SELECT `tag_id`, 
COUNT(`tag_id`) AS `value_occurrence` 
FROM `song_tag`
GROUP BY `tag_id`
ORDER BY `value_occurrence` DESC 
LIMIT 10;

How would I then get the tag name value from the tags table in the same query? Is it even possible? I have set up a fiddle with some dummy data and the three tables that are needed.(link at top of post).

This will be used in a wordpress wpdb query. I don't think there is much else to add about configuration setup, languages etc.

Answer 1

Answering your comment here so that I can format.

I personally don't know a way to get all tags in a single row with the song, however, we can create a SELECT statement that associates all tags to a song in different rows. It would look something like

SELECT s.title, t.name
FROM songs s LEFT JOIN song_tag st
    ON s.id = st.song_id
LEFT JOIN tags t
    ON st.tag_id = t.id
WHERE s.id = "123";

Answer 2

You would need to join the tags table to your result. So something like:

SELECT s.tag_id, COUNT(s.tag_id) AS value_occurrence, t.name
FROM song_tag s LEFT JOIN tags t
    ON s.tag_id = t.id
GROUP BY s.tag_id
ORDER BY value_occurrence DESC
LIMIT 10;