Constructing 3-step sql query I have:


a table of Characters with a character_id column; 
a join table of Chats with character_id and conversation_id columns; 
and a table of Conversations with a conversation_id column.


I am trying to construct a query that finds the Conversation between two characters (sender and recipient) and ONLY those two characters (other conversations may exist with sender, recipient, and other characters involved - group conversations). 

I'm using Ruby on Rails.

The query involves three steps: 


Find all Conversations belonging to sender. 
Select from these the ones that also belong to recipient. 
Select from these the one that belongs only to sender and recipient.


I can't get the query quite right. The best I've managed is below. 

Where am I going wrong? 

The query below gives this error: PG::SyntaxError: ERROR:  syntax error at or near "GROUP"

SELECT Conversations.id FROM
(
    (
        (
            Conversations INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{sender_id} ) AS sterling
                          ON Chats.conversation_id=Conversations.id
        )
        INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{recipient_id} ) AS archer
        ON Chats.conversation_id=Conversations.id
    )
    GROUP BY conversation_id
    HAVING COUNT(Chats.conversation_id)=2
);
Why not just use:

SELECT id FROM Conversations
INNER JOIN (
    SELECT conversation_id FROM Chats  WHERE Chats.character_id= '#{sender_id}' 
) AS sterling
    ON sterling.conversation_id = Conversations.id
INNER JOIN ( 
    SELECT conversation_id FROM Chats WHERE Chats.character_id = '#{recipient_id}' 
) AS archer
    ON archer.conversation_id = Conversations.id;


Since those are inner join, it has to be in both sender and recipient. It will only return conversation which are in both 3 tables. 

The count does not seem useful here unless you are trying to get something like people with more than 1 conversation together.

It should also work with this simpler version although it does not check for the existence of conversation_id in Conversations:

SELECT conversation_id 
FROM Chats AS sterling
INNER JOIN  Chats AS archer
    ON sterling.conversation_id = archer.conversation_id
WHERE sterling.character_id = '#{recipient_id}' 
    AND archer.character_id = '#{sender_id}';


Exit with count:

SELECT cv.conversation_id
FROM (
    SELECT conversation_id 
    FROM Chats AS sterling
    INNER JOIN  Chats AS archer
        ON sterling.conversation_id = archer.conversation_id
    WHERE sterling.character_id = '#{recipient_id}' 
    GROUP BY conversation_id 
    HAVING count(distinct character_id) = 2
) ce
INNER JOIN  Chats AS ch
        ON ce.conversation_id = ch.conversation_id
    WHERE ch.character_id = '#{sender_id}';