Get a row based on data from another table-CodePudding

I have the following tables:

dialogs:
id

and

users_dialogs:
user_id
dialog_id

How can I get (SELECT FROM ...) a dialog ID by two user IDs? Example:

dialogs:
id: 1

users_dialogs:
user_id: 2 | dialog_id: 1
user_id: 7 | dialog_id: 1

need return dialog with id = 1... idk how

CodePudding user response：

This query gets the user_ids and the dialog_ids from users_dialogs where the dialog_id exists in the dialogs table.

SELECT user_id, dialog_id FROM users_dialogs WHERE user_id IN (2,7)

Gives

users_dialogs:
user_id: 2 | dialog_id: 1
user_id: 7 | dialog_id: 1

You need to fill the user_ids programmatically in your code