I have a problem with an SQL query. I want to select every user that has unread messages.

I have three tables

1. users
2. messages
3. 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

1. not in object_visited (easy), or
2. 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.

CodePudding user response：

Join and aggregate as the following:

select U.user_id
from Users U left join object_visited O
on U.user_id = O.user_id
left join Messages M
on M.message_id =  O.message_id
group by U.user_id
having count(M.message_id) < (select count(message_id) from Messages)

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

CodePudding user response：

You appear to be describing some kind of broadcast messaging where all messages are to be seen by all users. This requires the cartesian product (CROSS JOIN) of users and messages and then the LEFT JOIN (or NOT EXISTS) to object_visited.

SELECT u.*, COUNT(m.message_id) unread_messages
FROM Users u
INNER JOIN Messages m
LEFT JOIN object_visited ov
    ON u.user_id = ov.user_id
    AND m.message_id = ov.message_id
WHERE ov.message_id IS NULL
GROUP BY u.user_id

Please excuse the lazy "borrowing" of ahmed's db<>fiddle