Recently I had to implement a “related tags” feature, i.e. you want to get all tags of items tagged with a specific tag. For example, if you have posts tagged with “sql” then you want to get all other tags of those “sql” posts, like “mysql”, “performance”, whatever.
Here the SQL I came up with for the aforementioned example:
SELECT Tag.name, COUNT(*) as postcount FROM tags AS t INNER JOIN posts_tags AS pt ON (t.id = pt.tag_id) INNER JOIN posts AS p ON (pt.post_id = p.id) INNER JOIN posts_tags AS pota ON (p.id = pota.post_id) INNER JOIN tags AS Tag ON (pota.tag_id = Tag.id) WHERE t.name = 'sql' AND t.id <> Tag.id GROUP BY Tag.name';
I hope this SQL snippet is useful for some of you!