title: Find rows that have same value in one column and other values in another column?
body: <p>I have a PostgreSQL database that stores users in a <code>users</code> table and conversations they take part in a <code>conversation</code> table. Since each user can take part in multiple conversations and each conversation can involve multiple users, I have a <code>conversation_user</code> linking table to track which users are participating in each conversation:</p>
  
  <pre><code># conversation_user
  id  |  conversation_id | user_id
  ----+------------------+--------
  1   |                1 |      32
  2   |                1 |       3
  3   |                2 |      32
  4   |                2 |       3
  5   |                2 |       4
  </code></pre>
  
  <p>In the above table, user 32 is having one conversation with just user 3 and another with both 3 and user 4.  How would I write a query that would show that there is a conversation between just user 32 and user 3?</p>
  
  <p>I've tried the following:</p>
  
  <pre><code>SELECT conversation_id AS cid,
         user_id
  FROM conversation_user
  GROUP BY cid HAVING count(*) = 2
  AND (user_id = 32
       OR user_id = 3);
  
  SELECT conversation_id AS cid,
     user_id
  FROM conversation_user
  GROUP BY (cid HAVING count(*) = 2
  AND (user_id = 32
       OR user_id = 3));
  
  SELECT conversation_id AS cid,
         user_id
  FROM conversation_user
  WHERE (user_id = 32)
    OR (user_id = 3)
  GROUP BY cid HAVING count(*) = 2;
  </code></pre>
  
  <p>These queries throw an error that says that user_id must appear in the <code>GROUP BY</code> clause or be used in an aggregate function. Putting them in an aggregate function (e.g. <code>MIN</code> or <code>MAX</code>) doesn't sound appropriate. I thought that my first two attempts were putting them in the <code>GROUP BY</code> clause. </p>
  
  <p>What am I doing wrong?</p>