CODEWITHSUNDEEP
mysql
Ravisha Hesh
Ravisha Hesh

Reputation: 1504

Get table 1 record that is common to mutiple table 2 records when there's a many to many relationship between tables

I have three tables, chats, users and chat_user, where chat can have mutiple users and users can have multiple chats. I want to get the chat that is common to all the given user ids.

chats (id)

users (id)

chat_user (id, chat_id, user_id)

I want to get chat that belongs to user 1 and 5 or multiple user ids

Edit

I'm using Laravel framework, so it's better if there's a way to get the result through one of parent tables(users, chats).

But any help would be appreciated.

Upvotes: 0

Views: 29

Answers (2)

Cid
Cid

Reputation: 15247

You may need to join the tables chats (the one that contains the informations you want) and the table chat_user (the one that contains the id of the chat and of the user) to apply where clause on the joined table.

In Laravel, I don't know.

SELECT c.*
FROM `chats` c
JOIN `chat_user` cu
    ON c.id = cu.chat_id
WHERE cu.chat_id NOT IN (
    SELECT cuu.chat_id
    FROM chat_user cuu
    WHERE cuu.user_id NOT IN (1, 5))

Upvotes: 1

Jahnavi Paliwal
Jahnavi Paliwal

Reputation: 1822

I am guessing that the userId will be an input parameter here? Try the query below:

SELECT
*
from
chats
WHERE
id in (
SELECT
  chat_id
from
  chat_user
WHERE
  user_id = '1'
  OR user_id = '5'
group by
  chat_id
having
  count(*) = 2
)

The count(*)=2 will ensure only those chats where both user 1 and 5 are present.

Upvotes: 0

Related Questions