Write a SQL query to determine which users have more than 10 unread messages-CodePudding

Table users have columns:

user_id,
username,
email,
friend_count

Table messages have columns:

message_id, 
from_user_id, 
to_user_id, 
date_sent, 
date_read, 
message

when date_read=NULL (we know that this is an unread msg)

Now I want to return the users (probably username) and the no of unread messages. I wrote the query below:

QUERY:

SELECT u.username, unread_msgs
FROM messages AS m
INNER JOIN users AS u ON m.to_user_id=u.user_id
WHERE unread_msgs = COUNT(date_read=”NULL’);

I know the count function is wrong because no subquery allowed in aggregate functions, can someone tell me what other way can i get the result that i want?

Thanks in advance.

CodePudding user response：

You can filter for messages where date_read is NULL and GROUP BY username.

SELECT u.username, COUNT(*) AS unread_msgs
FROM messages m
INNER JOIN users u ON m.to_user_id = u.user_id
WHERE date_read IS NULL
GROUP BY u.username

Alternatively, if you also want to see users who do not have unread messages.

SELECT u.username, SUM(CASE WHEN date_read IS NULL THEN 1 ELSE 0 END) AS unread_msgs
FROM messages m
INNER JOIN users u ON m.to_user_id = u.user_id
GROUP BY u.username

CodePudding user response：

SELECT u.username, count(m.message_id) as unread_msgs
FROM messages AS m
INNER JOIN users AS u ON m.to_user_id=u.user_id
WHERE m.date_read IS NULL
group by u.username
HAVING unread_msgs > 10;

CodePudding user response：

select  u.user_id,u.username,unread_m.unread_count from
(
  select to_user_id,count(message_id)unread_count
  from messages
  where date_read is null
  group by to_user_id
  having count(message_id)>10
)unread_m
join users as u on unread_m.to_user_id=u.user_id

CodePudding user response：

If I understand correctly, this might be what youre looking for:

SELECT u.username, unread_msgs
FROM messages AS m
INNER JOIN users AS u ON m.to_user_id=u.user_id
WHERE unread_msgs = (select count(*) where date_read is null)

Im a bit confused about what you want to do tho

CodePudding user response：

You can use correlated query to show the users who haven't read their messages. Also, null is a special value and can't use comparison like =.

select username,
       (select count(*) from messages b where b.to_user_id = a.user_id and date_read is null) unread_messages
from users a