CODEWITHSUNDEEP
sql
bigpotato
bigpotato

Reputation: 27507

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

?

Upvotes: 2

Views: 132

Answers (4)

Shawn
Shawn

Reputation: 4786

If you need other details from the chat, I'm assuming those would be kept in the chats table. To get to that, 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

Upvotes: 0

user8346348
user8346348

Reputation:

In your WHERE statement use IN:

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

I don't see why you would have to write it on the chats table when you have the data chat_id in the chat_users table. Unless there's more data you are looking to pull from the chats table, in which case you would JOIN the chats table.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Assuming a chat only occurs between two users, then we can simply aggregate the chat_users table 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

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

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)

Upvotes: 2

Related Questions