Number of unread messages between two users, MYSQL query
there are 2 tables like this
1st: id username userdata log pass
2nd: id idfrom idfor text isread date .....
I need to get, in a single query, the names and the number of unread messages for each user who has written to a given user
and at the same time have a list of names of all the contacts a given user has corresponded with
you could try it like this
SELECT *, IF(`idfrom` != :id_my, `idfrom`, `idfor`) AS user_not_me, SUM(`isread`) AS count_new FROM message WHERE (`idfrom` = :id_my OR `idfor` = :id_my) GROUP BY user_notme
this gives the number of unread messages for both the first and second participants of the dialogue, summed together. To get the term meant to output the count for only one of the interlocutors:
SELECT *, IF(`idfrom` != :id_my, `idfrom`, `idfor`) AS user_notme, SUM(`isread` * IF(`idfor` = :id_my, 1, 0)) AS count_new FROM message WHERE (`idfrom` = :id_my OR `idfor` = :id_my) GROUP BY user_notme
but how do I get the usernames and the number of unread messages at the same time? A JOIN?
SELECT m.*
, u.username
, if(fromuserid != :id_my, fromuserid, foruserid) AS user_notme
, sum(if(m.isread, 0, 1) * if(foruserid = :id_my, 1, 0)) AS count_new
FROM
message m
JOIN user u
ON (u.id = if(fromuserid != :id_my, fromuserid, foruserid))
WHERE
(fromuserid = :id_my
OR foruserid = :id_my)
GROUP BY
user_notme
solved it
Comments