Finding rows with identical values (PostgreSQL)-CodePudding

I am currently doing a chat feature using PostgreSQL, and the way I store the conversations is the members of each conversation is stored in a separate table called conversationMember.

| convomemberId | conversationId                       | userId                               |
-----------------------------------------------------------------------------------------------
| UUID          | ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2 | d2119e47-b122-41b5-a425-afde47fd36ba |
-----------------------------------------------------------------------------------------------
| UUID          | ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2 | f9a3572c-3424-408f-8c06-6422728ef847 |

As you can see I store each row with the conversationId and the userId so that I can easily check which user is in a conversation. But currently I now need a function to check if a conversation between 2 users exist in the DB. So I would like to know if there is a way for me to do a SQL query to get the conversationId based on 2 userIds.

E.g.
Input: userId: "d2119e47-b122-41b5-a425-afde47fd36ba" and userId: "f9a3572c-3424-408f-8c06-6422728ef847"
Output: conversationId: "ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2"

CodePudding user response：

You can do that (Result here)

select c1.conversation_id
from conversation c1, conversation c2
where c1.conversation_id = c2.conversation_id
and c1.user_id = 'd2119e47-b122-41b5-a425-afde47fd36ba'
and c2.user_id = 'f9a3572c-3424-408f-8c06-6422728ef847';

CodePudding user response：

try this select:

select cm.userId, dup.conversationId from 
       (select count(*) cnt, conversationId from conversationMember group by conversationId having count(*)>1) dup, 
        conversationMember cm 
        where cm.conversationId=dup.conversationId;

the result should be:

cm.userId                               dup.conversationId 
d2119e47-b122-41b5-a425-afde47fd36ba    ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2 
f9a3572c-3424-408f-8c06-6422728ef847    ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2

CodePudding user response：

select t1.conversationId 
       from conversationMember t1 inner join conversationMember t2 on 
       (t1.conversationId=t2.conversationId) 
   where t1.userId='d2119e47-b122-41b5-a425-afde47fd36ba' 
     and t2.userid='f9a3572c-3424-408f-8c06-6422728ef847';

result

            conversationid
--------------------------------------
 ddbde2ae-17f3-47da-94d2-a3dffd9ee7e2