Many-to-Many Joins and Bridge Tables

SELECT u.user_id, u.email, r.role_name
FROM users u
JOIN user_roles ur ON ur.user_id = u.user_id
JOIN roles r ON r.role_id = ur.role_id;

# Resolve many-to-many relationships with two joins.

SELECT p.post_id, p.title, COUNT(pt.tag_id) AS tag_count
FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.post_id
GROUP BY p.post_id, p.title;

# Count related entities after joining through the bridge.

SELECT pt.post_id
FROM post_tags pt
JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name IN ('sql', 'performance')
GROUP BY pt.post_id
HAVING COUNT(DISTINCT t.name) = 2;

# Join and group, then compare distinct matched tags to the required count.

CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);

# Index both sides of the relationship for join speed.