CODEWITHSUNDEEP
mysql
z0mbieKale
z0mbieKale

Reputation: 1028

Select all from table where user_id exists in both tables

I am creating a small chat/ message system. I have four tables: thread, thread_participant, message and message_read_state. I know it might not be the best solution, but I just wanted to give this a go for a project of mine. How can i select all new messages coming in for a specific user without selecting the his/her own message?

thread table

id
----------
1
2

thread_participant table

thread_id         user_id
----------        -------- 
1                 6
1                 7

message table

id                thread_id    send_date    message     sending_user_id
----------        --------     --------     --------    --------
1                 1            2016-05-12   HELLO       7 
2                 1            2016-05-12   HELLO BACK  6

message_read_state

message_id   user_id s    read_date    
-----        --------     --------   
1            6            2016-05-12  
2            7            2016-05-12

EDIT

What I have so far. This select all the threads I have participated, but I would like to display only the incoming message, without my response.

SELECT message.id, message.send_date, message.message, tbl_users.user_id, message.thread_id
, (SELECT message_read_state.read_date 
   FROM message_read_state 
   WHERE message_read_state.message_id = message.id 
     and message_read_state.user_id = 6) AS ReadState
FROM message INNER JOIN tbl_users ON message.sending_user_id = tbl_users.user_id
WHERE ( message.id in 
        ( SELECT (message.id)
          FROM thread_participant INNER JOIN message 
            ON thread_participant.thread_id = message.thread_id
          WHERE thread_participant.user_id = 6
          GROUP BY thread_participant.user_id
        )
      )
ORDER BY message.send_date ASC;

Upvotes: 1

Views: 210

Answers (1)

Blank
Blank

Reputation: 12378

What about this;)

SELECT message.id, message.send_date, message.message, tbl_users.user_id, message.thread_id
, (SELECT message_read_state.read_date 
   FROM message_read_state 
   WHERE message_read_state.message_id = message.id 
     and message_read_state.user_id = 6) AS ReadState
FROM message INNER JOIN tbl_users ON message.sending_user_id = tbl_users.user_id
WHERE ( message.id in 
        ( SELECT (message.id)
          FROM thread_participant INNER JOIN message 
            ON thread_participant.thread_id = message.thread_id
          WHERE thread_participant.user_id = 6
          GROUP BY thread_participant.user_id
        )
      )
AND message.sending_user_id <> 6
ORDER BY message.send_date ASC;

I've just add AND message.sending_user_id <> 6 in your original sql. If you don't want response, just let sending_user_id is not you, then you get what you want. :D

Upvotes: 1

Related Questions