Edmund Edmund - 1 year ago 41
SQL Question

SQL: How to find a record with 2 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 has these records:

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


Given I have 2 user_ids
1
and
2
, how would I write a query to the chats table to find a chat with:

1) a chat_user with user_id ==
1


AND

2) a chat_user with user_id ==
2


?

Answer Source

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

SELECT * from chat_users where 
user_id = 1 AND chat_id in (
    SELECT chat_id from chat_users where user_id = 2)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download