CODEWITHSUNDEEP
mysqlsqlselectjoin
Pioz
Pioz

Reputation: 6321

SQL select many to many relationship

I've the following schema:

Users
-----
id
name

Conversations
-------------
id
other

Partecipants (join table)
------------
id
user_id
conversation_id
other

An user can have many conversations and a conversation belongs to many users.

I need to select all the conversations of an user with a subset of other users.

My try is (do not work):

SELECT     *
FROM       `conversations` 
INNER JOIN `participants` ON `conversations`.`id` = `participants`.`conversation_id` 
WHERE      `participants`.`user_id` = 1 
AND         (participants.user_id IN (4,6)) 
GROUP BY    participants.conversation_id

Any idea?

Upvotes: 0

Views: 367

Answers (2)

Basit Munir
Basit Munir

Reputation: 438

What I understand from your questions was you want to see users "4,6" involved in conversation with user_id = 1. to do so try following query.

 select * from (SELECT     conversations.id as conversation_id, participants.user_id as selectedUser,   GROUP_CONCAT(participants.user_id) AS participants
 FROM       `conversations` 
 INNER JOIN `participants` ON `conversations`.`id` = `participants`.`conversation_id` 
GROUP BY    participants.conversation_id ) as derivedTable 
where 
selectedUser=1 and 
(participants like '%4%' and participants like '%6%')

enter image description here

what above query does is. initially it will take all record from conversation and participants table and concat all participants agains user_id=1. then outer query checks are pretty clear to find user_id and participants have 4 and 6.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Hmmm. Here is a method using group by and having:

select p.conversation_id
from participants p
group by p.conversation_id
having sum(p.user_id = 1) > 0 and     -- user 1 in conversation
       sum(p.user_id in (4, 6)) > 0;   -- user 4 and/or 6 in conversation

Upvotes: 3

Related Questions