CODEWITHSUNDEEP
mysqlsqldatabase
RockDaFox
RockDaFox

Reputation: 237

Looking for SQL query for a user chat

I'm working on a user chat and i haven't found the right SQL query to get all messages column, get all member column who send and all member column who receive only with the user x and user y.

I don't want to create row like : receiving_mem_id and sending_mem_id in message because there is another type of chat with some users in a "room".

Here is my database: enter image description here

Can you give me some help?

Here is the query I've tried using:

Select  me.mem_id
      , me.mem_nickname
      , me.isConnected
      , me.isBreeder
      , m.mess_id
      , m.mess_text
      , m.mess_date
From    PSU.Member As me
      , message As m
      , send As s
Where   me.mem_id = s.mem_id
        And s.mess_id = m.mess_id
        And s.mess_id In (Select    re.mess_id
                          From      message As m
                                  , PSU.Member As me
                                  , receive As re
                          Where     m.mess_id = re.mess_id
                                    And re.mem_id = (Select mem_id
                                                     From   PSU.Member
                                                     Where  mem_id = :mem_id
                                                    ));

But the problem I'm having is that any member can see messages sent to the target member.

Upvotes: 1

Views: 1473

Answers (1)

vtuhtan
vtuhtan

Reputation: 1066

EDIT: According to your comment, I modified the query. If you need to filter sent messages on some field from member table, add a join and filter data. 

select me.mem_id
  , me.mem_nickname
  , me.isConnected
  , me.isBreeder
  , m.mess_id
  , m.mess_text
  , m.mess_date
from member me 
join receive r on me.mem_id = r.mem_id
join message m on r.mess_id = m.mess_id
where me.mem_id = :mem_id
and m.mess_id in (
    select mess_id 
    from send s
    where mem_id = :another_mem_id
)
order by m.mess_date desc

Upvotes: 1

Related Questions