Join Query for Count in mysql statement

Question

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.

score 1 · Accepted Answer · answered Jul 25 '16 at 15:31

1

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;

answered Jul 25 '16 at 15:31

BrandonM

390
4
12

1

Hi Brandon, that worked a treat thank you. Have to wait to accept answer but will – Danny Connolly Jul 25 '16 at 15:33
Just out of curiosity @BrandonM how would I go about selecting one song based on id and grab all tags associated with the one song in one query? – Danny Connolly Jul 25 '16 at 15:52

score 1 · Answer 2 · answered Jul 25 '16 at 16:00

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";

answered Jul 25 '16 at 16:00

BrandonM

390
4
12

Yeah I wasn't sure if was possible to get all tags in one row. Thanks for your help on that. – Danny Connolly Jul 25 '16 at 16:12
I seem to remember that it is possible? Just not sure how. – BrandonM Jul 25 '16 at 16:24
http://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql There is a pseudo way to do it. Doesn't created multiple columns though. – BrandonM Jul 25 '16 at 16:27

Join Query for Count in mysql statement

2 Answers2