Maykonn Maykonn - 3 months ago 7
MySQL Question

MySQL: Retrieve results related to two id of users

EDIT: I know, my database isn't normalized, but I can't change it, thanks.

I have a table called

user_dialogs
with
id, user_id, dialog_id, type, status, created_at
and
updated_at
fields.

I not found a good way to retrieve an existent
dialog_id
between
user_id 1
and
user_id 2
with
type='PRIVATE'
.

I try to use IN:
user_id IN(1,2) and type='PRIVATE'
but i get
dialog_id
that user 1 made with user 3 for example.

What is the best way to get the dialog_id just between user 1 and 2?

How to call this problem/algorithm? I don't know.

Thanks in advance!

Answer

My understanding is that you want to find dialog_id's shared between user 1 and user 2 with type='PRIVATE' for both.

One solution is to search for all dialog_id's for the user 1 with type = 'PRIVATE', then search dialog_id's for user 2 with type = 'PRIVATE' which are in common with user 1

SELECT ud.dialog_id
FROM user_dialogs ud
WHERE type='PRIVATE'
AND user_id = 2
AND ud.dialog_id IN (
    SELECT dialog_id
    FROM user_dialogs
    WHERE type='PRIVATE'
    AND user_id = 1)