[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