Clever Geek Handbook

SQL: How do I find a record with two associations with separate WHERE clauses?

Tables:

users
id: int // users has_many chat_users

chats
id: int // chats has_many chat_users

chat_users
id: int
chat_id: int (foreign key) // chat_users belongs_to chat
user_id: int (foreign key) // chat_users belongs_to user

The database contains the following entries:

users, id: 1
users, id: 2
users, id: 3

chats, id: 1 // <---------
chats, id: 2

chat_users, id: 1, chat_id: 1, user_id: 1 // <-------
chat_users, id: 2, chat_id: 1, user_id: 2 // <-------
chat_users, id: 3, chat_id: 2, user_id: 2
chat_users, id: 4, chat_id: 2, user_id: 3

Considering I have 2 user_ids 1

and 2

how would I write a query to the chat table to find the chat using:

1) chat_user with user_id == 1

and

2) chat_user with user_id == 2

?

+3


source to share


4 answers


You need to select all chat_ids with one of the users, then select all chats that also have a second user:



SELECT * from chat_users where 
user_id = 1 AND chat_id in (
    SELECT chat_id from chat_users where user_id = 2)
+2


source


Assuming the chat only occurs between two users, we can simply aggregate the table chat_users

by id

and check that the minimum user_id

is one and the maximum user_id

is two.



SELECT id
FROM chat_users
GROUP BY id
HAVING MIN(user_id) = 1 AND MAX(user_id) = 2
+2


source


In your expression, WHERE

use IN

:

SELECT * FROM chat_users WHERE user_id IN (1, 2)

I don't understand why you need to write it to the table chats

when you have data chat_id

in the table chat_users

. If there is no data that you want to retrieve from the table chats

, in which case you would be a JOIN

table chats

.

0


source


If you want other details from the chat, I assume they will be stored in a table chats

. For this use

SELECT DISTINCT c.id AS chatID,  <other chats.cols>
FROM chats c
INNER JOIN (
    SELECT c1.chat_id 
    FROM chat_users c1 
    INNER JOIN chat_users c2 ON c1.chat_id = c2.chat_id
        AND c2.user_id = 2
    WHERE c1.user_id = 1
) c3 ON c.id = c3.chat_id
0


source






More articles:


All Articles