CODEWITHSUNDEEP
mysql
Deepak Rathore
Deepak Rathore

Reputation: 617

Fetching number of unread messages from MySQL

I am developing an Android chat application. I have two tables.

Table: chats

id  sender_id receiver_id message  read_status  sender_name

1    22         11        hi..      1          raaj
2    22         11        hey.      1          raaj
3    22         11        nice.     0          raaj
4    22         11        wow       0          raaj
5    22         11        thanx     0          raaj
6    11         22        yup       0          deep
7    33         11        hi..      1          sanju

Table read:

id   status
0     unread
1     read 

About users
id--> 22 -->raaj

id-->11--->deep

id--->33--->sanju

I have to get DISTINCT sender_id , sender_name , no. of unread messages WHERE sender_id != '11'

I need result something like this:

sender_id   sender_name  no_of_unread_messages
22          raaj            3
33          sanju           0

How can I do this in MySQL using one query?

Upvotes: 0

Views: 285

Answers (1)

Alex
Alex

Reputation: 17289

SELECT sender_id, sender_name, COUNT(*) no_of_unread_messages
FROM chats
WHERE sender_id != 11
  AND read_status=0
GROUP BY sender_id

if you need records with SUM = 0 try this one:

SELECT sender_id, sender_name, SUM(IF(read_status=0,1,0)) no_of_unread_messages
FROM chats
WHERE sender_id != 11
GROUP BY sender_id

Upvotes: 1

Related Questions