sql - Mysql test if 2 users have a conversation -

i making message system, on facebook. when user send new message person profile, instead of within messages, want check database if have conversation together.

my tables like:

messages =>   m_id (message id)   t_id (thread id)   author_id   text  thread_recipients =>   t_id (thread id)   user_id (id of user belonging thread/conversation)   is_read 

so have row each user belonging conversation, , every message has thread belongs to.

so lets have user_id 14 , user im writing has 16. need find out if these rows existed:

t_id    user_id    is_read  x         16         1  x         14         1 

the thread id's have match, , there should not other users in thread.

can done in 1 query?

join table thrice:

select tr1.t_id  thread_recepients tr1   join thread_recepients tr2 on tr2.t_id = tr1.t_id      , tr2.user_id = 16 left join thread_recepients tr3 on on tr3.t_id = tr1.t_id     , tr3.user_id not in (14, 16) tr1.user_id = 14 , tr3.user_id null 

the is null test asserts no other users participated (no other rows joined) in conversation, per request:

can not other users belonging thread

because want left joined rows other users not found.

---

recommended indexes:

create index thread_recepients_t_id on thread_recepients (t_id); create index thread_recepients_user_id on thread_recepients (user_id);