Danny Connolly Danny Connolly - 1 year ago 62
SQL Question

Join Query for Count in mysql statement

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

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download