Need help with MySQL query & PHP

Question

I have the following 3 tables:

(PK = Primary Key, FK = Foreign Key)

Files Table

File ID (PK)    File Name    ...
------------    ---------
     1            a.jpg      ...
     2            b.png      ...
     3            c.jpg      ...
     .              .
     .              .
     .              .

Tags Table

Tag ID (PK)   Tag Name       ...
-----------   ----------  
   1          Melbourne      ...
   2          April          ...
   3          2010           ...
   .           .
   .           .
   .           .

Files_Tags Table

File ID (FK)    Tag ID (FK)
------------    -----------
      1              1
      1              5
      1              7 
      2              2 
      2              4
      3              3
      .              .
      .              .
      .              .

In PHP, I want to get a list of all tags along with the number of times the tag appears (i.e. the number of files that have this tag).

Is that possible to do with one MySQL query ?

Answer 1

Try GROUP BY on your tag id. Use a LEFT JOIN to include tags that exist in the tags table but aren't ever used.

SELECT
    Tag_Name, 
    COUNT(Files_Tags.Tag_ID) AS cnt
FROM Tags
LEFT JOIN Files_Tags
ON Tags.Tag_ID = Files_Tags.Tag_ID
GROUP BY Tags.Tag_ID

Result:

Melbourne  1
April      1
2010       1
...        ...

You may also want to add an ORDER BY Tag_Name or an ORDER BY COUNT(*) if you want the rows returned in sorted order.

---

Daniel Vassello also submitted an answer but deleted it. However his answer is quite easy to adapt to meet your new requirements. Here is his solution, modified to use a LEFT JOIN instead of an INNER JOIN:

SELECT t.tag_id,
       t.tag_name,
       IFNULL(d.tag_count, 0) AS tag_count
FROM tags t
LEFT JOIN
(
    SELECT tag_id, COUNT(*) tag_count
    FROM files_tags
    GROUP BY tag_id
) d ON d.tag_id = t.tag_id;