CODEWITHSUNDEEP
mysql
CainaSouza
CainaSouza

Reputation: 1437

MySQL - Selecting common value

I'm trying to create a query but I don't know how.

I have three tables: chat, chat_user and user

Something like:

 ---------      ------------------------       ------------------
|  chat   |    |        chat_user       |     |       user       |
 ---------      ------------------------       ------------------
| id |  1 |    | id | chat_id | user_id |     | id | external_id | 
 ---- ----      ---- --------- ---------       ---- -------------
               | 1  |    1    |    1    |     |  1 |     111     |
               | 2  |    1    |    2    |     |  2 |     222     |
                ---- --------- ---------       ---- -------------

I need to select one chat.id FOR TWO users. In the example above, the users 111 and 222 are sharing the chat.id 1. What I need is to get as a select result the chat.id 1 (as the example above) only once when searching external_id 111 and 222.

I found some answer here but they didn't help a lot... I always get other chat.id values.

If you need any extra explanation, I'm right here to answer.

Thanks in advance!

Upvotes: 0

Views: 251

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is actually an example of a "set-within-sets" query: you are asking about whether the set of users for a given chat contain certain users. I like to solve such questions with aggregation and a having clause, because this is a very general approach.

Here is the query:

select c.*
from chat_user cu join
     user u
     on cu.user_id = u.id join
     chat c
     on cu.chat_id = c.id
group by cu.chat_id
having sum(u.external_id = '111') > 0 and
       sum(u.external_id = '222') > 0

This is aggregating by the chat in chat_user. The first condition in the having clause says: "Is user 111 present?". The second says: "Is user 222 present?" Only chat ids with examples of both users are returned.

Upvotes: 4

Oswald
Oswald

Reputation: 31655

SELECT id FROM chat AS c
INNER JOIN chat_user AS cu1 ON c.id = cu1.chat_id
INNER JOIN chat_user AS cu2 ON c.id = cu2.chat_id AND cu1.id < cu2.id
INNER JOIN user AS u1 ON u1.id = cu1.user_id
INNER JOIN user AS u2 ON u2.id = cu2.user_id
WHERE u1.external_id = 111
AND u2.external_id = 222

The AND cu1.id < cu2.id part forces the chat_user with id 1 to be joined es cu1 and the chat_user with id 2 to be joined as cu2. This eliminates the record where the chat_user with id 2 is joined as cu1 and the chat_user with id 1 is joined as cu2. My guess is that you omitted this clause in your attempts and this resulted in duplicates.

Upvotes: 2

Related Questions