MySql Query for getting field value as count

Question

T1:

    +--------------+------------------+------+-----+---------+----------------+
    | Field        | Type             | Null | Key | Default | Extra          |
    +--------------+------------------+------+-----+---------+----------------+
    | id           | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
    | message_text | text             | NO   |     | NULL    |                |
    | sender_id    | int(11) unsigned | NO   | MUL | NULL    |                |
    | receiver_id  | int(11) unsigned | NO   | MUL | NULL    |                |
    | pair_id      | int(11) unsigned | NO   | MUL | NULL    |                |
    | time_stamp   | bigint(20)       | NO   |     | NULL    |                |
    | is_read      | tinyint(1)       | NO   |     | 0       |                |
    +--------------+------------------+------+-----+---------+----------------+

T2:

    +----------------+------------------+------+-----+---------+----------------+
    | Field          | Type             | Null | Key | Default | Extra          |
    +----------------+------------------+------+-----+---------+----------------+
    | id             | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
    | user_id1       | int(11) unsigned | NO   | MUL | NULL    |                |
    | user_id2       | int(11) unsigned | NO   | MUL | NULL    |                |
    +----------------+------------------+------+-----+---------+----------------+

I want to fetch the records from this table like:

 pair_id | message_text | sender_id | receiver_id | count_unread

This table(T1) stores the messages where:

    message_text: message,
    sender_id: sender,
    receiver_id: receiver,
    pair_id: This is the foreign key referring to an other table that keeps the chatting user pairs                 i.e. T2,
    is_read: 0 if unread, otherwise 1.

Now, i want all the pairs with the corresponding unread messages count. Also, the pair must get displayed even if the count is zero. Thanks in advance.

Query i have tried:

    SELECT all_messages.id, 
           all_messages.sender_id, 
           all_messages.receiver_id, 
           count(all_messages.id) AS unread_msg_count 
      FROM all_messages 
           JOIN message_pairs 
                ON message_pairs.id = all_messages.pair_id 
     WHERE all_messages.receiver_id = :receiver_id 
           AND is_read = 0 
    GROUP BY 
           pair_id

Are your tables called T1, T2 or all_messages, message_pairs? -- What does ":receiver_id" mean? -- Did you try to run the query? What did you get? — Daniel Sparing
– Daniel Sparing, Commented Mar 3, 2014 at 12:08

Daniel Sparing · Accepted Answer · 2014-03-03 12:10:05Z

1

You can't list the message contents in the same relation as the one you are using for counting, because if you count then one row corresponds to a pair with many messages.

You can count like:

    SELECT message_pairs.*, 
           count(all_messages.id) AS unread_msg_count 
      FROM all_messages 
           RIGHT JOIN message_pairs 
                ON message_pairs.id = all_messages.pair_id 
     WHERE is_read = 0 
    GROUP BY 
           message_pairs.*;

answered Mar 3, 2014 at 12:10

Daniel Sparing

2,18316 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

MySql Query for getting field value as count

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