How to count between two MySQL tables using MySQL and PHP?

Question

For example, how can I count how many times the tag HTML is displayed in the a_id column and then display all the tags that are related to the a_id column in alphabetical order.

Here is the MySQL tables

CREATE TABLE a_tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_id INT UNSIGNED NOT NULL,
a_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

Here is whats in the MySQL tables

TABLE tags
id  tag
1  HTML
2  HTML
3  CSS
4  PHP
5  HTML

TABLE a_tags
id  tag_id  a_id
1  1   3
2  2   2
3  3   3
4  4   3
5  5   3

And Here is the code.

$result = mysql_query("SELECT a_tags.*, tags.* 
        FROM a_tags 
        INNER JOIN tags ON tags.id = a_tags.tag_id 
        WHERE a_tags.users_a_id=3
        ORDER BY users_a_id DESC");

Answer 1

Woudln't

SELECT * FROM `a_tags` WHERE a_id = (SELECT `id` FROM `tags` WHERE `tag` = 'HTML')

work?

Edit: This might not be what you want it to do. If you could clarify your question, it'd be helpful.

Answer 2

I don't quite see what the foreign keys are but assuming that a_tags.a_id maps to tags.id then try this.

SELECT tags.tag,COUNT(a_tags.a_id) FROM a_tags
JOIN tags ON a_tags.a_id = tags.id
GROUP BY a_tags.a_id