Select and count query from two tables

Question

I must retrive all those who have sent messages to a given user and if the messages have not been read, return the number. I have two tables

users

| id_user| username | status |
|    1   |   user1  |   yes  |
|    2   |   user2  |   yes  |
|    3   |   user3  |   yes  |

messages

| id_message |id_sender|id_dest|    message  | read |
|     1      |    1    |   2   |   some text | yes  |
|     2      |    3    |   2   |   some text |  no  |
|     3      |    2    |   1   |   some text |  no  |

and I have this query

select id_sender,username, count(distinct   id_message) as nr_messages
            FROM messages
            INNER JOIN users
            ON id_sender = id_user
            WHERE id_dest=$dest and status='yes'
            GROUP by id_sender

I can count the number of messages for each user but I can not know how many are unread. I must have a number if the messages are unread and 0 i the user do not have unread messages. for example for user2

 sender: user3 (1 unread message)
 sender: user1 (0 unred message)

messages table is ~2000000 rows

I don't see receiver_id in the schema as you have in query. — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 7, 2015 at 16:05
@sgeddes I dont undestand your answer can you explain or make an example? thanks — Alberto Alibaba
– Alberto Alibaba, Commented Mar 7, 2015 at 16:11

Abhik Chakraborty · Accepted Answer · 2015-03-07 16:18:24Z

1

If you need to count both read and unread at the same time, you can use conditional sum as

select 
m.id_sender,
u.username,
sum(m.`read` = 'yes') as read_messages,
sum(m.`read` <> 'yes') as unread_messages 
from messages m 
join users u on u.id_user = m.id_sender 
where m.id_dest = 2 
group by m.id_sender ;

For the above sample data you will get

+-----------+----------+---------------+-----------------+
| id_sender | username | read_messages | unread_messages |
+-----------+----------+---------------+-----------------+
|         1 | user1    |             1 |               0 |
|         3 | user3    |             0 |               1 |
+-----------+----------+---------------+-----------------+

answered Mar 7, 2015 at 16:18

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select and count query from two tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related