Sunday, 1 July 2018

Mysql get the users which didn't have any messages to a certain user?

I have the following message table enter image description here
I managed to get the users that have messages with a certain user using this query in php
SELECT ReceiverUserId,SenderUserId,Name,Email,Count(CASE WHEN IsReaded = 0 THEN 1 END)
 as messagesNum,Message,MessageDateTime FROM (SELECT * FROM
messages order by
MessageDateTime DESC) AS footbl
join users on users.id =footbl.SenderUserId
where ReceiverUserId=".$user_id.
" group by SenderUserId

It returned the users that have messages with a certain user ($user_id) but it didn't return the users that the certain user send them messages with out any replies from them, I tried another query for this purpose
SELECT * FROM `messages` as m
    join `users` on m.`ReceiverUserId`=`users`.Id
    WHERE (select count(*) from `messages` as s
    WHERE m.`SenderUserId`=s.`ReceiverUserId` ) = 0
    AND `SenderUserId`=".$user_id.
    " group by `SenderUserId`

For Example if I have the following data enter image description here
The first Query (if $user_id=2) return the following result
enter image description here
The Second Query (if $user_id=2) returns nothing
How Should I know that 2 send messages to 4 and 5 too?


from Mysql get the users which didn't have any messages to a certain user?

No comments:

Post a Comment