CODEWITHSUNDEEP
mysqlsqlsubqueryinner-join
Rodhesia
Rodhesia

Reputation: 13

Check if two users in the same chat

I need to be able to get only if there are matches on 2 different user id and match the 'chats_idchats' with the 'idchats' Query used:

SELECT *
  FROM chats_has_usuarios chu
 INNER JOIN chats c
    ON c.idchats = chu.chats_idchats
 WHERE chu.chats_idchats = c.idchats
   AND chu.usuarios_idusuarios = 1
    OR chu.usuarios_idusuarios = 2
chats_idchats usuarios_idusuarios idchats fecha
8 1 8 2020-12-15
8 2 8 2020-12-15
9 2 9 2020-12-15

I want to get

chats_idchats usuarios_idusuarios idchats fecha
8 1 8 2020-12-15
8 2 8 2020-12-15

Upvotes: 0

Views: 59

Answers (4)

MR Mark II
MR Mark II

Reputation: 453

Try:

SELECT * FROM chats_has_usuarios chu
DISTINCT chu.usuarios_idusuarios
INNER JOIN chats c ON c.idchats = chu.chats_idchats
WHERE chu.chats_idchats = c.idchats
AND chu.usuarios_idusuarios = 1
OR chu.usuarios_idusuarios = 2

If you want to use with any ids:

SELECT * FROM chats_has_usuarios chu
DISTINCT chu.usuarios_idusuarios
INNER JOIN chats c ON c.idchats = chu.chats_idchats
WHERE chu.chats_idchats = c.idchats

Upvotes: 0

GMB
GMB

Reputation: 222582

You can do this with exists:

select chu.*, c.fecha
from chats_has_usuarios chu
inner join chats c on c.idchat = chu.chats_has_usuarios 
where 
    usuarios_idusuarios in (1, 2)
    and exists (
        select 1
        from chats_has_usuarios chu1
        where 
            chu1.usuarios_idusuarios in (1, 2) 
            and chu1.chats_idchats = chu.chats_idchats
            and chu1.usuarios_idusuarios <> chu.usuarios_idusuarios
    )

Assuming that the same user does not appear twice in the same chat, another option is window functions, if you are running MySQL 8.0 or higher:

select chu.*, c.fecha
from (
    select chu.*, count(*) over(partition by chats_idchats) cnt
    from chats_has_usuarios chu
    where usuarios_idusuarios in (1, 2) 
) chu
inner join chats c on c.idchat = chu.chats_has_usuarios 
where chu.cnt > 1

Upvotes: 1

ElapsedSoul
ElapsedSoul

Reputation: 825

Do you mean this:

SELECT * FROM chats_has_usuarios chu
INNER JOIN chats c ON c.idchats = chu.chats_idchats
WHERE chu.chats_idchats = c.idchats
AND exists(select 1 from chats_has_usuarios abc 
where chu.chats_idchats = abc.chats_idchats 
and chu.usuarios_idusuarios <> abc.usuarios_idusuarios)

base on usuarios_idusuarios is the userid

Upvotes: 1

Ehsan Gerayli
Ehsan Gerayli

Reputation: 594

You can use this

select * from (SELECT * FROM chats_has_usuarios chu
INNER JOIN chats c ON c.idchats = chu.chats_idchats
WHERE chu.chats_idchats = c.idchats
AND chu.usuarios_idusuarios = 1
OR chu.usuarios_idusuarios = 2) t1
,

(SELECT * FROM chats_has_usuarios chu
INNER JOIN chats c ON c.idchats = chu.chats_idchats
WHERE chu.chats_idchats = c.idchats
AND chu.usuarios_idusuarios = 1
OR chu.usuarios_idusuarios = 2) t2

where t1.usuarios_idusuarios < t2.usuarios_idusuarios and
and t1.chats_idchats=t2.chats_idchats

Upvotes: 0

Related Questions