Conversation Schemas & Data

DATABASE runs ON PostgreSQL 9.3.4 ON Debian "wheezy"

TABLE "public.auth_user"
    COLUMN    |           TYPE           |                       Modifiers
--------------+--------------------------+--------------------------------------------------------
 id           | INTEGER                  | NOT NULL DEFAULT NEXTVAL('auth_user_id_seq'::regclass)
 password     | CHARACTER VARYING(128)   | NOT NULL
 username     | CHARACTER VARYING(30)    | NOT NULL
Indexes:
    "auth_user_pkey" PRIMARY KEY, btree (id)
    "auth_user_username_key" UNIQUE CONSTRAINT, btree (username)
    "auth_user_username_like" btree (username varchar_pattern_ops)
Referenced BY:
    TABLE "conversation_user" CONSTRAINT "conversation_user_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "message" CONSTRAINT "message_from_user_id_fkey" FOREIGN KEY (from_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED


TABLE "public.conversation"
   COLUMN   |           TYPE           |                         Modifiers
------------+--------------------------+-----------------------------------------------------------
 id         | INTEGER                  | NOT NULL DEFAULT NEXTVAL('conversation_id_seq'::regclass)
 start_date | TIMESTAMP WITH TIME zone | NOT NULL
Indexes:
    "conversation_pkey" PRIMARY KEY, btree (id)
Referenced BY:
    TABLE "conversation_user" CONSTRAINT "conversation_id_refs_id_4344ca71" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "message" CONSTRAINT "message_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED


TABLE "public.conversation_user"
     COLUMN      |  TYPE   |                           Modifiers
-----------------+---------+----------------------------------------------------------------
 id              | INTEGER | NOT NULL DEFAULT NEXTVAL('conversation_user_id_seq'::regclass)
 conversation_id | INTEGER | NOT NULL
 user_id         | INTEGER | NOT NULL
Indexes:
    "conversation_user_pkey" PRIMARY KEY, btree (id)
    "conversation_user_conversation_id_user_id_key" UNIQUE CONSTRAINT, btree (conversation_id, user_id)
    "conversation_user_conversation_id" btree (conversation_id)
    "conversation_user_user_id" btree (user_id)
Foreign-KEY constraints:
    "conversation_id_refs_id_4344ca71" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED
    "conversation_user_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED


auth_user TABLE DATA:
 id |   username
----+---------------
  1 | user1
  3 | user3
  4 | user4
 32 | me
(4 ROWS)


conversation TABLE DATA:
 id |          start_date
----+-------------------------------
  1 | 2015-01-05 12:14:24.098392-08
  2 | 2015-01-05 12:14:24.124386-08
  3 | 2015-01-05 12:14:24.143839-08
(3 ROWS)


 id | conversation_id | user_id
----+-----------------+---------
  1 |               1 |      32
  2 |               1 |       3
  3 |               2 |      32
  4 |               2 |       3
  5 |               2 |       4
  6 |               3 |       3
  7 |               3 |       1
(7 ROWS)

-- Your audited query altered to determine if there's a conversation
-- between users 32, 3, and 4
SELECT cu1.id
FROM   conversation_user cu1
WHERE  cu1.user_id = 32
AND    EXISTS (
   SELECT 1
   FROM   conversation_user cu2
   WHERE  cu2.conversation_id = cu1.conversation_id
   AND    cu2.user_id = 3
   )
AND    EXISTS (
   SELECT 1
   FROM   conversation_user cu3
   WHERE  cu3.conversation_id = cu1.conversation_id
   AND    cu3.user_id = 4
   )
AND NOT EXISTS (
   SELECT 1
   FROM   conversation_user cu4
   WHERE  cu4.conversation_id = cu1.conversation_id
   AND    cu4.user_id NOT IN (3,4,32)
   );