MySQL query to fetch posts that all users seen

Question

I need to fetch posts that all users seen from MySQL database contains users, posts and users_seen_posts tables.

users_seen_posts table:

id |  user_id  | post_id  | 
---------------------------
 1 |  xxxxx4   | xxxxx1   |
 2 |  xxxxx3   | xxxxx6   |

Answer 1

This type of query is called "relational division." There are a couple of ways to solve it in SQL, but it's kind of uncommon so all the methods in SQL are a bit awkward.

Here's one solution, that compares the count of all users to the count of distinct user_id's found in the users_seen_posts table:

SELECT s.post_id
FROM users AS u
LEFT OUTER JOIN users_seen_posts AS s ON u.id = s.user_id
GROUP BY s.post_id
HAVING COUNT(*) = COUNT(DISTINCT s.user_id)

If the counts are equal, it means every user is represented for a given post, therefore all users have seen that post.