I have a problem with an SQL query.
I want to select every user that has unread messages.
I have three tables
- users
- messages
- object_visited (has an entry [user_id,message_id], if the user has read the message)
So I make a selection of messages and what I need is every user that is either
- not in object_visited (easy), or
- doesn’t have an entry for every message that I select.
The problem I face is that I simply cannot visualize how I need to filter and join those tables together to get the desired result.
Edit:
Users:
user_id | user_name |
---|---|
11111 | User1 |
22222 | User2 |
33333 | User3 |
Messages:
message_id | content |
---|---|
aaaaa | Hello World |
bbbbb | This is a message |
ccccc | test test 123 |
object_visited:
user_id | message_id |
---|---|
11111 | aaaaa |
11111 | bbbbb |
11111 | ccccc |
33333 | aaaaa |
33333 | ccccc |
User1 has read every message, User2 has not read any messages, and User3 has not read bbbbb(This is a message) .
The query should return:
user_id |
---|
22222 |
33333 |
As they don’t have an entry object_visited for every message.
2
Answers
Join and aggregate as the following:
Noting that the null values will not be counted by the count function, so the count of messages for user 2222 is 0.
For user 3333 the count of messages is 2.
Both counts (0 and 2) are less than the count of all messages in the messages table (3).
See demo
Assuming you have the appropriate FK constraints (to avoid orphans) on the junction table, there is no need to join to
Messages
–