SQL query to match table with multiple rows from another

Question

Apologies for the poorly worded question.

I have two tables, called conversations and conversation_users. Each conversation can have multiple users attached to it. The fields are as follows:

conversations

conv_id
conv_author_id
conv_date

conversation_users

cu_id
cu_conv_id
cu_user_id

Now, to avoid duplication of conversations with the same users. I want to check whether a conversation already exists with users with ID, say 12 and 25. If one exists, I wish to obtain the ID of that conversation.

How do I go about structuring a query to do this?

How are the multiple users stored on the conversations table? Are there multiple rows with the same conv_id and different conv_author_id values? — Mike
– Mike, Commented Nov 6, 2013 at 11:56
All users participating in a conversation are stored in conversation_users. The author_id is just to track who started the conversation. — emkay
– emkay, Commented Nov 6, 2013 at 11:57
@emkay just saw that you added the tag before answering my comment — Srini V
– Srini V, Commented Nov 6, 2013 at 11:57

Tim Schmelter · Accepted Answer · 2013-11-06 11:57:05Z

3

I want to check whether a conversation already exists with users with ID, say 12 and 25.

You can use EXISTS:

SELECT CASE WHEN EXISTS
(
    SELECT 1 FROM conversation_users cu
    WHERE cu.cu_conv_id = c.conv_id
    AND   cu.cu_user_id IN (12, 25)
) THEN 'Yes, these users are in this conversation' 
  ELSE 'This conversation is between different users' END AS Result
FROM conversations c

answered Nov 6, 2013 at 11:57

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query to match table with multiple rows from another

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related