CODEWITHSUNDEEP
mysqlsql
Nicholas Klatte
Nicholas Klatte

Reputation: 45

SELECT entries if they don't match every entry in another table [edit]

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.

Upvotes: 0

Views: 50

Answers (2)

user1191247
user1191247

Reputation: 12998

Assuming you have the appropriate FK constraints (to avoid orphans) on the junction table, there is no need to join to Messages -

SELECT u.user_id, (SELECT COUNT(message_id) from Messages) - COUNT(ov.message_id) AS `unread`
FROM Users u
LEFT JOIN object_visited ov
    ON u.user_id = ov.user_id
GROUP BY u.user_id
HAVING `unread` > 0;

Upvotes: 0

ahmed
ahmed

Reputation: 9181

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

Upvotes: 1

Related Questions