Find All Many-to-many Relationships Which Are Tied To An Arbitrary Number Of Other Many-to-many Relationships
Join the DZone community and get the full member experience.Join For Free
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.
Opinions expressed by DZone contributors are their own.