CODEWITHSUNDEEP
mysqlsql
AndrewTet
AndrewTet

Reputation: 1182

Mysql select the same info in multiple rows

I'm working on the database for a messaging system between users of an app. Right now all the relevant information for the messaging system is based in 2 tables. The first table contains all the information about the conversation between the two users (including the conversation id), the second is basically just a list of all the users who have access to the conversation, the third is a table of all the messages. Below is a visualization of the relevant parts of each table

Table 1:

|  convoId  |
-------------
|     1     |
|     2     |
|     3     |
|     4     |
|     5     |

Table 2:

|  convoId  |  userId  |
------------------------
|     1     |    4     |
|     1     |    5     |
|     2     |    4     |
|     2     |    5     |
|     2     |    6     |
|     3     |    3     |
|     3     |    4     |

So basically, conversation 1 is between users 4 and 5, which conversation 2 is between users 4, 5, and 6.

What I'm trying to do is, when a user goes to send a message, see if there is already a conversation between that user and the one (or ones) they are trying to message. So if user 4 was trying to message user 5, I would want to get convoId 1, but if 4 was trying to message user 7 it would create a new conversation (I've already got that part handled).

I can think of a way to do this using multiple queries and looping through them, but it seems a bit heavy handed. I was wondering if there was a way to do it in a single query (a maybe just a few).

Thanks

Upvotes: 1

Views: 58

Answers (1)

MinhD
MinhD

Reputation: 1810

Try:

SELECT a.convoId 
FROM Table2 a JOIN Table2 b
ON a.convoId = b.convoId
WHERE a.userId = 4
AND b.userId = 5
AND a.convoId IN (SELECT convoId FROM Table2
                   GROUP BY convoId
                   HAVING COUNT(DISTINCT userId) = 2);

This will return convoId between user 4 and 5. If no data, these users did not have any conversation yet.

Upvotes: 2

Related Questions