[RESOLVED] Tag cloud, counting across tables.

[Justin]

I am trying to build a tag cloud list for a page I'm making and need a little bit of help. I know how I could do this with a few loops and a bunch of queries but would prefer a nicer solution.

I have two tables, a tagmap that maps the tag id to a file id, and a tag table that maps tag ids to names. What I am currently doing is selecting all the tags from the table and displaying them, what I would like to do is also add the count of records that have that tag id in the tagmap table.

I would like to select tags.id, tags.name, and a count of tag_id found in the tagmap table.

tagmap

+----+---------+--------+
| id | file_id | tag_id |
+----+---------+--------+
| 25 | 4 | 6 |
| 15 | 3 | 6 |
| 14 | 3 | 3 |
| 18 | 4 | 8 |
| 26 | 4 | 7 |
| 19 | 3 | 9 |
| 20 | 4 | 10 |
| 21 | 5 | 11 |
+----+---------+--------+


tags

+--------+-----------+
| tag_id | name |
+--------+-----------+
| 3 | test |
| 6 | rit |
| 7 | calendar |
| 8 | dates |
| 9 | classes |
| 10 | important |
| 11 | passwords |
+--------+-----------+


In this example the query would return a count of 2 for tag rit (tag_id 6), and one for the others.

How I can implement it is to just select all the records from the tags table and then loop over the results asking for counts. Is there a nicer way to do this with sql?

[ssystems]

SELECT
t1.id
,t1.tag_count,
t2.name
FROM
tags t2
JOIN
(SELECT COUNT(id) as tag_count, id FROM tagmaps GROUP BY id) As t1
ON
t2.tag_id = t1.tag_id

[Justin]

Thank you, just needed a few tweaks with field names but now it works!

Thanks again, that was exactly what I needed.


SELECT
t1.tag_id,
t1.tag_count,
t2.name
FROM
tags t2
JOIN
(SELECT COUNT( id ) AS tag_count, tag_id FROM tagmap GROUP BY tag_id) AS t1
ON
t2.tag_id = t1.tag_id