Subquery and Join

Question

I have 3 tables that related my question;

User => u_id | first_name

Conversation => c_id | bla bla

ConversaionMember => c_id | u_id | bla bla

What i'm asking is that, I want to have all information about a conversation that has user 3 (i mean user id) with all the other users that is in that conversation

As an example

Conversation 

1

2

ConversationMember (c_id,u_id)

1 1

1 2

1 3

2 1

2 3

I want a query that gives me that result when im asking for user 2 Which includes all information about conversation that user 2 included in

ConversationMember (c_id,u_id)

    1 1

    1 2

    1 3

Answer 1

The following query will return all the records from the conversation member table for conversations that user number 3 is part of

SELECT * FROM `ConversationMember` WHERE `c_id` IN (SELECT `c_id` FROM `ConversationMember` WHERE `u_id`=3)

There is no need to create joins

Answer 2

If I understand correctly, you want all rows for conversions in ConversationMember where one of the members is 2 (or whatever). If so:

select cm.*
from ConversationMember cm
where exists (select 1
              from ConversationMember cm2
              where cm2.u_id = 2 and
                    cm2.c_id = c.c_id
             );