DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Find All Many-to-many Relationships Which Are Tied To An Arbitrary Number Of Other Many-to-many Relationships

04.03.2005
| 16360 views |
  • submit to reddit
        Yet more code from Snippets itself. When you narrow down posts by tags, I only want you to see tags which are shared by other posts which also have the same current tags. This is more difficult than it sounds, and requires use of a subselect:

SELECT *, COUNT(pt.post_id) AS count FROM posts_tags pt, tags t WHERE pt.post_id IN (SELECT pt.post_id FROM posts_tags pt, tags t WHERE pt.tag_id = t.id AND (t.name IN ('rails', 'ruby')) GROUP BY pt.post_id HAVING COUNT(pt.post_id)=2) AND t.id = pt.tag_id GROUP BY pt.tag_id ORDER BY count DESC;
The first query (inside the subselect) finds all post IDs for posts which definitely contain any of the current tags. The outer query finds all OTHER tags associated with these posts, therefore we find all (and the quantity of) the tags related to the input tags.