How to select specific fields from pivot table that matches exact values

[Dadod]

I have 3 tables

• conversations - I save basic information about the conversation

• users - Infor about user

• conversation_users - table that saves information about which user belongs to which conversation

In my case user “1” wants to get a conversation between him and users: 3, 4, 5. So I’m trying to fetch conversation_id = 12. First I need to find all conversations user “1” belongs to, that is conversation_id “1” and “12”.

Next I’m executing SELECT * FROM conversations_users WHERE conversation_id in (1,12). How do I pick rows marked in red rectangle?

[DaveMaxwell]

The quick answer is you just modify the query…

SELECT * FROM conversations_users WHERE conversation_id = 12

But what I think you’re looking for is something more like

SELECT c.conversationID
       , c.conversationfield1
       , c.conversationfield2
       , u.field1
       , u.field1
  FROM (SELECT conversation_id 
          FROM conversation_users 
         WHERE user_id = 1) SQ
    JOIN conversation C ON C.conversation_id = SQ.conversation_id
    JOIN conversation_users CU ON C.conversation_id = CU.conversation_id
    JOIN users U ON CU.user_id = U.user_id
ORDER BY c.conversation_id
       , u.user_id

Note: Obviously change the query fields accordingly as the only ones I actually know are the ones used out of conversation_users…

The sub-query will pull JUST the conversations which user_id of 1 has participated in. That list is then tied to conversations which ties to conversation_users which ties to users. But doing is this way allows you to pull information for all users involved with those conversations without resorting to ugly sub-queries…

[Dadod]

That seems very heavy, I solved it like this:

I did it like this:
You might have conversation_id = 1, with users (1,3)
and conversation_id = 12, with users (1,3,4,5)

SELECT conversation_id
FROM conversations_users
WHERE user_id IN (1,3)
GROUP BY conversation_id
HAVING COUNT(user_id) = 2

In that case you will get 2 results with conversation_id = 1 and 12
But if you make WHERE user_id IN (1,3,4,5) AND HAVING COUNT(user_id) = 4, you’ll get one result conversation_id = 12

So 2nd query is

SELECT conversation_id, count(conversation_id) as count
FROM conversations_users
WHERE conversation_id IN (1,12)
GROUP BY conversation_id
HAVING count = 2;

[DaveMaxwell]

What EXACTLY are you trying to accomplish? Are you trying to get conversations where ONLY 1,3,4,5 are involved? Do they ALL have to be involved? Or can it be some, say 1,3 and 4? What if it’s a conversation with more than those users involved?

If you’re looking for conversations where ONLY 1,3,4 and 5 are involved, your query is going to return false positives. Add a record for conversation 12 with a userid of 2 and run your first query - you’ll still see it. Is that what you want?

If not, you’re going to need to include a select which excludes those conversations where the user id is not in your list.

SELECT conversation_id
  FROM conversation_users 
 WHERE user_id in (1,3,4,5)
   AND conversation_id NOT IN (SELECT conversation_id
                                 FROM conversation_users 
                                WHERE user_id NOT IN (1,3,4,5))

If you’re looking for the query where ALL MUST be involved, then this is probably your best bet. Seems counterintuitive, but it will return only those conversations where ALL of the users are involved. If you don’t care about additional users, remove the second AND condition as that is the filtering out the conversations with users not on the list.

 SELECT c.conversation_id
  FROM conversation_users C
  JOIN (SELECT conversation_id 
          FROM conversation_users 
         WHERE user_id = 3) C3 ON C.conversation_id = C3.conversation_id
  JOIN (SELECT conversation_id 
          FROM conversation_users 
         WHERE user_id = 4) C4 ON C.conversation_id = C4.conversation_id
  JOIN (SELECT conversation_id 
          FROM conversation_users 
         WHERE user_id = 5) C5 ON C.conversation_id = C5.conversation_id
 WHERE C.user_id = 1
   AND c.conversation_id NOT IN (SELECT conversation_id
                                   FROM conversation_users 
                                  WHERE user_id NOT IN (1,3,4,5))

[Dadod]

All participants should match, so if I look for WHERE user_id in (1,3,4) it should find only that one conversation, it should not find (1,3,4,5)

[DaveMaxwell]

So in your last example, should it find conversation 1 which only has users 1 and 3 on it?

If not, thinking about it, your approach will be easier to construct on the fly than mine, but will still need to include the exclusion pattern

SELECT conversation_id
  FROM conversations_users
 WHERE user_id IN (1,3)
   AND conversation_id NOT IN (SELECT conversation_id 
                                 FROM conversation_users
                                WHERE user_id NOT IN (1,3))
GROUP BY conversation_id
HAVING COUNT(user_id) = 2

Or your latest example (two where clauses and having changes)…

SELECT conversation_id
  FROM conversations_users
 WHERE user_id IN (1,3,4)
   AND conversation_id NOT IN (SELECT conversation_id 
                                 FROM conversation_users
                                WHERE user_id NOT IN (1,3,4))
GROUP BY conversation_id
HAVING COUNT(user_id) = 3