CODEWITHSUNDEEP
mysqlsql
Fabrizio Fenoglio
Fabrizio Fenoglio

Reputation: 5947

select rows in mysql having another column with same value

I have a table that manage conversations of a chat between users, the structure is the following.

id | user_id | conversation_id

let's say that on the conversation with ID 1 there are 3 people to chat and the conversation with ID 2, 2 people as well

Conversations_users table will look like this

   id | user_id | conversation_id
    1      1              1
    2      2              1
    3      4              1
    4      3              2
    5      4              2

Now having only the id of the users 3 and 4 and Not Conversation ID I would like select the conversation that belongs to that users so a verbal query should be:

Select from conversations_users, where in user_id = 3 and 4 and conversation_id is equals to conversation id of user 3 and 4

how can I build this "verbal query" in Mysql?

Upvotes: 1

Views: 4144

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Here is one method:

select uc.conversation_id
from UserConversions uc
where uc.user_id in (3, 4)
group by uc.conversation_id
having count(*) = 2;

If the table could have duplicates, you'll want: having count(distinct user_id) = 2.

EDIT:

If you want a specific list, just move the where condition to the having clause:

select cu.conversation_id
from conversations_users cu
group by cu.conversation_id
having sum(cu.user_id in (3, 4)) = 2 and
       sum(cu.user_id not in (3, 4)) = 0;

Upvotes: 1

Jonathan
Jonathan

Reputation: 1564

to get all the users in the conversations that user 3 and 4 are part of you could use this: 

select distinct(user_id) from conversation_table where conversation_id in (select distinct(conversation_id) from conversation_table where user_id in (3,4));

it won't be very fast though

to get their actual conversations, I'm assuming you have a different table with the text in it:

you probably want something like this

select distinct(u.user_id), c.text from conversation_table u left join conversations c on c.id=u.conversation_id where u.conversation_id in (select distinct(conversation_id) from conversation_table where user_id in (3,4));

here is an sqlfiddle

Upvotes: 2

rcorty
rcorty

Reputation: 1200

I assume you have another table called "conversations" which holds the data you really want.

SELECT *
FROM conversations, conversations_users
WHERE conversations_users.user_id in (3,4)
    AND conversations.id = conversations_users.conversation_id

Upvotes: 0

Related Questions